쿼리 힌트(Transact-SQL)

{ HASH | ORDER } GROUP

쿼리의 GROUP BY 또는 DISTINCT 절에 지정된 집계에서 해시나 정렬을 사용하도록 지정합니다.

{ MERGE | HASH | CONCAT } UNION

UNION 집합을 병합, 해시 또는 연결하여 모든 UNION 연산을 수행하도록 지정합니다. 둘 이상의 UNION 힌트를 지정한 경우 쿼리 최적화 프로그램에서는 지정한 힌트 중 가장 부담이 적은 전략을 선택합니다.

{ LOOP | MERGE | HASH } JOIN

전체 쿼리에서 모든 조인 연산이 LOOP JOIN, MERGE JOIN 또는 HASH JOIN에 의해 수행되도록 지정합니다. 조인 힌트를 둘 이상 지정한 경우 최적화 프로그램에서는 허용되는 힌트 중 가장 부담이 적은 조인 방법을 선택합니다.

같은 쿼리에서 특정 테이블 쌍에 대해 FROM 절에 조인 힌트도 지정한 경우 두 테이블의 조인에서 조인 힌트가 우선적으로 적용되지만 쿼리 힌트도 고려됩니다. 따라서 테이블 쌍에 대한 조인 힌트는 쿼리 힌트에서 허용되는 조인 방법의 선택만 제한하게 될 수도 있습니다. 자세한 내용은 조인 힌트(Transact-SQL)를 참조하십시오.

EXPAND VIEWS

인덱싱된 뷰를 확장하고 쿼리 최적화 프로그램에서 인덱싱된 뷰를 쿼리 일부를 대체하는 것으로 간주하지 않도록 지정합니다. 쿼리 텍스트에 있는 뷰 정의에 의해 뷰 이름이 바뀌면 뷰가 확장됩니다.

이 쿼리 힌트는 쿼리 계획에서 인덱싱된 뷰와 인덱싱된 뷰의 인덱스를 직접 사용하도록 허용하지 않습니다.

인덱싱된 뷰는 쿼리의 SELECT 부분에서 뷰를 직접 참조하고 WITH (NOEXPAND) 또는 WITH (NOEXPAND, INDEX( index_value [ ,...n ] ) )가 지정된 경우에만 확장되지 않습니다. 쿼리 힌트 WITH (NOEXPAND)에 대한 자세한 내용은 FROM을 참조하십시오.

INSERT, UPDATE, MERGE 및 DELETE 문을 비롯한 문의 SELECT 부분에 있는 뷰만 힌트의 영향을 받습니다.

FAST number_rows

첫 번째 음수가 아닌 정수 number_rows를 빨리 검색하기 위해 쿼리를 최적화하도록 지정합니다. 첫 번째 number_rows를 반환한 후에 쿼리는 계속 실행하여 전체 결과 집합을 만듭니다.

FORCE ORDER

쿼리 구문에 지정된 조인 순서가 쿼리 최적화 시 유지되도록 지정합니다. FORCE ORDER를 사용해도 쿼리 최적화 프로그램이 취할 수 있는 역할 반전 동작에는 영향을 미치지 않습니다.

참고
MERGE 문에서 WHEN SOURCE NOT MATCHED 절이 지정되어 있지 않으면 원본 테이블은 기본 조인 순서에 따라 대상 테이블보다 먼저 액세스됩니다. FORCE ORDER를 지정하면 이러한 기본 동작이 유지됩니다.

KEEP PLAN

쿼리 최적화 프로그램에서 쿼리에 대한 예상 다시 컴파일 임계값을 완화하도록 합니다. 예상된 다시 컴파일 임계값은 테이블에 UPDATE, DELETE, MERGE 또는 INSERT 문을 실행하여 인덱싱된 열을 예상 수만큼 변경했을 때 쿼리가 자동으로 다시 컴파일되는 시점입니다. 테이블이 자주 업데이트되는 경우 KEEP PLAN을 지정하면 쿼리가 지나치게 자주 다시 컴파일되지 않아 유용합니다.

KEEPFIXED PLAN

통계 변경 시에는 최적화 프로그램이 쿼리를 다시 컴파일하지 않도록 합니다. KEEPFIXED PLAN을 지정하면 원본으로 사용하는 테이블의 스키마가 바뀌거나 테이블에 대해 sp_recompile이 실행되는 경우에만 쿼리를 다시 컴파일합니다.

IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX

쿼리에 비클러스터형 xVelocity 메모리 최적화 Columnstore 인덱스가 사용되지 않도록 방지합니다. 쿼리에 Columnstore 인덱스 사용을 방지하기 위한 쿼리 힌트와 Columnstore 인덱스를 사용하기 위한 인덱스 힌트가 포함되어 있으면 힌트가 충돌하게 되고 오류가 반환됩니다.

MAXDOP number

이 옵션을 지정하는 쿼리에 대해 sp_configure 및 리소스 관리자의 최대 병렬 처리 수준 구성 옵션을 무시합니다. MAXDOP 쿼리 힌트는 sp_configure로 구성한 값을 초과할 수 있습니다. MAXDOP가 리소스 관리자로 구성한 값을 초과하는 경우 데이터베이스 엔진는 ALTER WORKLOAD GROUP(Transact-SQL)에 설명된 리소스 관리자 MAXDOP 값을 사용합니다. max degree of parallelism 구성 옵션에 사용된 모든 의미 체계 규칙을 MAXDOP 쿼리 힌트 사용 시 적용할 수 있습니다. 자세한 내용은 max degree of parallelism 서버 구성 옵션 구성을 참조하십시오.

주의
MAXDOP가 0으로 설정되면 서버는 최대 병렬 처리 수준을 선택합니다.

MAXRECURSION number

해당 쿼리에 대해 허용되는 최대 재귀 횟수를 지정합니다. number는 0에서 32767 사이의 음수가 아닌 정수입니다. 0을 지정하면 제한이 적용되지 않습니다. 이 옵션을 지정하지 않은 경우 서버에 대한 기본 한도는 100입니다.

쿼리 실행 중에 MAXRECURSION 한도로 지정된 횟수 또는 기본 횟수에 도달하면 쿼리가 종료되고 오류가 반환됩니다.

이 오류로 인해 문의 모든 결과가 롤백됩니다. 문이 SELECT 문인 경우 결과의 일부가 반환되거나 아무런 결과도 반환되지 않을 수 있습니다. 반환된 일부 결과에는 지정한 최대 재귀 수준을 초과한 재귀 수준의 모든 행이 포함되지 않을 수 있습니다.

자세한 내용은 WITH common_table_expression(Transact-SQL)을 참조하십시오.

OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )

쿼리가 컴파일되고 최적화될 때 쿼리 최적화 프로그램이 지역 변수에 대해 특정 값을 사용하도록 지시합니다. 해당 값은 쿼리 최적화 중에만 사용되고 쿼리 실행 중에는 사용되지 않습니다.

@variable_name

쿼리에서 사용된 지역 변수의 이름이며 OPTIMIZE FOR 쿼리 힌트와 함께 사용하도록 값을 할당할 수 있습니다.

UNKNOWN

쿼리 최적화 프로그램이 쿼리 최적화 동안 초기 값 대신 통계 데이터를 사용하여 지역 변수 값을 결정하도록 지정합니다.

literal_constant

OPTIMIZE FOR 쿼리 힌트와 함께 사용하도록 @variable_name을 할당할 리터럴 상수 값입니다. literal_constant는 쿼리 최적화 중에만 사용되며 쿼리 실행 중에는 @variable_name의 값으로 사용되지 않습니다. literal_constant는 리터럴 상수로 표현할 수 있는 모든 SQL Server 시스템 데이터 형식이 될 수 있습니다. literal_constant의 데이터 형식은 쿼리에서 @variable_name이 참조하는 데이터 형식으로 암시적으로 변환될 수 있어야 합니다.

OPTIMIZE FOR는 최적화 프로그램의 기본 매개 변수 감지 동작을 무효로 만들 수 있으며 계획 지침을 만들 때 사용할 수 있습니다. 자세한 내용은 저장 프로시저 다시 컴파일을 참조하십시오.

OPTIMIZE FOR UNKNOWN

쿼리가 컴파일 및 최적화될 때 쿼리 최적화 프로그램이 강제 매개 변수화를 통해 만든 매개 변수를 비롯한 모든 지역 변수에 대해 초기 값 대신 통계 데이터를 사용하도록 지시합니다.

같은 쿼리 힌트에서 OPTIMIZE FOR @variable_name = literal_constant 및 OPTIMIZE FOR UNKNOWN을 사용하면 쿼리 최적화 프로그램이 특정 값에는 지정된 literal_constant를 사용하고 나머지 변수 값에는 UNKNOWN을 사용합니다. 해당 값은 쿼리 최적화 중에만 사용되고 쿼리 실행 중에는 사용되지 않습니다.

PARAMETERIZATION { SIMPLE | FORCED }

SQL Server 쿼리 최적화 프로그램에서 쿼리 컴파일 시 적용하는 매개 변수화 규칙을 지정합니다.

중요
PARAMETERIZATION 쿼리 힌트는 계획 지침 내에서만 지정할 수 있습니다. 쿼리 내에서 직접 지정할 수는 없습니다.

SIMPLE은 쿼리 최적화 프로그램이 단순 매개 변수화를 시도하도록 지시합니다. FORCED는 최적화 프로그램이 강제 매개 변수화를 시도하도록 지시합니다. PARAMETERIZATION 쿼리 힌트는 계획 지침 내에서 PARAMETERIZATION 데이터베이스 SET 옵션의 현재 설정을 무시하는 데 사용됩니다. 자세한 내용은 계획 지침을 사용하여 쿼리 매개 변수화 동작 지정을 참조하십시오.

RECOMPILE

쿼리를 실행한 후 SQL Server 데이터베이스 엔진에서 해당 쿼리에 대해 생성된 계획을 삭제하도록 하여 다음에 같은 쿼리가 실행될 때 쿼리 최적화 프로그램이 쿼리 계획을 다시 컴파일하도록 지시합니다. RECOMPILE을 지정하지 않으면 데이터베이스 엔진은 쿼리 계획을 캐시하여 다시 사용합니다. 쿼리 계획을 컴파일할 때 RECOMPILE 쿼리 힌트는 쿼리에 있는 지역 변수의 현재 값을 사용하며 쿼리가 저장 프로시저 안에 있는 경우 매개 변수에 전달된 현재 값을 사용합니다.

RECOMPILE은 전체 저장 프로시저가 아닌 저장 프로시저 내 쿼리의 하위 집합만 다시 컴파일해야 하는 경우 WITH RECOMPILE 절을 사용하는 저장 프로시저를 만드는 데 유용합니다. 자세한 내용은 저장 프로시저 다시 컴파일을 참조하십시오. RECOMPILE은 계획 지침을 만들 때도 유용합니다.

ROBUST PLAN

쿼리 최적화 프로그램에서 성능이 저하되더라도 잠재적 최대 행 크기를 정의할 수 있는 계획을 세우도록 합니다. 쿼리가 처리될 때 중간 테이블 및 연산자가 입력 행보다 큰 행을 저장하고 처리해야 할 수 있습니다. 행이 너무 커서 특정 연산자가 행을 처리하지 못하는 경우도 있습니다. 이런 경우 데이터베이스 엔진에서는 쿼리 실행 중에 오류를 생성합니다. ROBUST PLAN을 사용하면 쿼리 최적화 프로그램에서 이러한 문제가 발생할 수 있는 쿼리 계획을 고려하지 않도록 할 수 있습니다.

이러한 계획이 불가능할 경우 쿼리 최적화 프로그램은 쿼리 실행 시 오류를 검색하도록 지연시키지 않고 오류를 반환합니다. 행에는 가변 길이 열이 포함될 수 있으며 데이터베이스 엔진은 데이터베이스 엔진에서 처리할 수 있는 범위 이상의 잠재적 최대 크기를 가진 행을 정의하도록 허용합니다. 그러나 대개 응용 프로그램은 데이터베이스 엔진이 처리할 수 있는 한도 내의 실제 크기를 가진 행을 저장합니다. 너무 긴 행이 있으면 데이터베이스 엔진에서 실행 오류를 반환합니다.

USE PLAN N'xml_plan'

쿼리 최적화 프로그램이 'xml_plan'에 의해 지정된 쿼리에 대해 기존의 쿼리 계획을 사용하도록 합니다. USE PLAN은 INSERT, UPDATE, MERGE 또는 DELETE 문에서 지정할 수 없습니다.

TABLE HINT (exposed_object_name [ , <table_hint> [ [, ]...n ] ] )

지정된 테이블 힌트를 exposed_object_name에 해당하는 테이블 또는 뷰에 적용합니다. 테이블 힌트는 계획 지침의 컨텍스트에서 쿼리 힌트로만 사용하는 것이 좋습니다.

exposed_object_name은 다음 참조 중 하나일 수 있습니다.

• 쿼리의 FROM 절에서 테이블 또는 뷰에 별칭을 사용하는 경우 exposed_object_name은 해당 별칭입니다.

• 별칭을 사용하지 않는 경우 exposed_object_name은 FROM 절에서 참조되는 테이블 또는 뷰와 일치합니다. 예를 들어 테이블 또는 뷰가 두 부분으로 된 이름을 사용하여 참조되는 경우 exposed_object_name도 똑같이 두 부분으로 된 이름입니다.

테이블 힌트는 지정하지 않고 exposed_object_name만 지정하면 쿼리에 개체에 대한 테이블 힌트의 일부로 지정된 인덱스가 모두 무시되고 쿼리 최적화 프로그램에서 인덱스 사용 여부를 결정합니다. 이 방법은 원래 쿼리를 수정할 수 없을 때 INDEX 테이블 힌트의 효과를 제거하는 데 이용할 수 있습니다. 자세한 내용은 예 10을 참조하십시오.

<table_hint> ::= { [ NOEXPAND ] { INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FORCESEEK [(index_value(index_column_name [,... ] )) ]| FORCESCAN | HOLDLOCK | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE |SPATIAL_WINDOW_MAX_CELLS | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }

exposed_object_name에 해당하는 테이블 또는 뷰에 쿼리 힌트로 적용할 테이블 힌트입니다. 이러한 힌트에 대한 자세한 내용은 테이블 힌트(Transact-SQL)를 참조하십시오.

쿼리에 테이블 힌트를 지정하는 WITH 절이 없다면 INDEX, FORCESCAN 및 FORCESEEK 이외의 테이블 힌트를 쿼리 힌트로 사용할 수 없습니다. 자세한 내용은 설명 부분을 참조하십시오.

주의
매개 변수와 함께 FORCESEEK를 지정할 경우 매개 변수 없이 FORCESEEK를 지정할 때보다 최적화 프로그램에서 고려할 수 있는 계획 수가 더 제한됩니다. 이로 인해 "계획을 생성할 수 없음" 오류가 많은 사례에서 발생하는 원인이 될 수도 있습니다. 후속 릴리스에서는 더 많은 계획을 고려할 수 있도록 최적화 프로그램이 수정될 것입니다.

쿼리 힌트는 문 내에 SELECT 절이 사용되는 경우를 제외하고 INSERT 문에서 지정할 수 없습니다.

쿼리 힌트는 하위 쿼리가 아닌 최상위 쿼리에서만 지정할 수 있습니다. 테이블 힌트를 쿼리 힌트로 지정하면 해당 힌트를 최상위 쿼리나 하위 쿼리에 지정할 수 있지만 TABLE HINT 절에서 exposed_object_name에 대해 지정한 값이 쿼리 또는 하위 쿼리의 표시 이름과 일치해야 합니다.

테이블 힌트를 쿼리 힌트로 지정

1.MERGE JOIN 사용

USE AdventureWorks2012;
GO
SELECT * 
FROM Sales.Customer AS c
INNER JOIN Sales.vStoreWithAddresses AS sa 
    ON c.CustomerID = sa.BusinessEntityID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO

2.OPTIMIZE FOR 사용

USE AdventureWorks2012;
GO
DECLARE @city_name nvarchar(30);
DECLARE @postal_code nvarchar(15);
SET @city_name = 'Ascheim';
SET @postal_code = 86171;
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );
GO

3.MAXRECURSION 사용

USE AdventureWorks2012;
GO
--Creates an infinite loop
WITH cte (CustomerID, PersonID, StoreID) AS
(
    SELECT CustomerID, PersonID, StoreID
    FROM Sales.Customer
    WHERE PersonID IS NOT NULL
  UNION ALL
    SELECT cte.CustomerID, cte.PersonID, cte.StoreID
    FROM cte 
    JOIN  Sales.Customer AS e 
        ON cte.PersonID = e.CustomerID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT CustomerID, PersonID, StoreID
FROM cte
OPTION (MAXRECURSION 2);
GO

4.MERGE UNION 사용

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e1
UNION
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO

5.HASH GROUP 및 FAST 사용

USE AdventureWorks2012;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10);
GO

6.MAXDOP 사용

USE AdventureWorks2012 ;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (MAXDOP 2);
GO

7.INDEX 사용

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE e.OrganizationLevel = 2;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e, INDEX (IX_Employee_OrganizationLevel_OrganizationNode)))';
GO
EXEC sp_create_plan_guide 
    @name = N'Guide2', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE e.OrganizationLevel = 2;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e, INDEX(PK_Employee_BusinessEntityID, IX_Employee_OrganizationLevel_OrganizationNode)))';
GO

8.FORCESEEK 사용

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide3', 
    @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.JobTitle
              FROM HumanResources.Employee
              JOIN Person.Person AS c ON HumanResources.Employee.BusinessEntityID = c.BusinessEntityID
              WHERE HumanResources.Employee.OrganizationLevel = 3
              ORDER BY c.LastName, c.FirstName;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT( HumanResources.Employee, FORCESEEK))';
GO

9.여러 테이블 힌트 사용

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide4', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode ) ) 
                       , TABLE HINT ( c, FORCESEEK) )';
GO

10.TABLE HINT를 사용하여 기존 테이블 힌트 다시 정의

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide5', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_OrganizationLevel_OrganizationNode))
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e))';
GO

11.의미 체계에 영향을 주는 테이블 힌트 지정

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide6', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 3;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_OrganizationLevel_OrganizationNode) , NOLOCK, FORCESEEK ))';
GO

USE AdventureWorks2012;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide7', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.JobTitle
              FROM HumanResources.Employee AS e 
              JOIN Person.Person AS c ON e.BusinessEntityID = c.BusinessEntityID
              WHERE OrganizationLevel = 2;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, NOLOCK))';
GO

참조