반응형
반응형

쿼리 힌트는 해당 힌트가 전체 쿼리에 사용해야 하는 힌트임을 나타냅니다. 쿼리 힌트는 문의 모든 연산자에 영향을 줍니다. 기본 쿼리에 UNION이 포함된 경우 UNION 연산과 연관된 마지막 쿼리에만 OPTION 절을 포함할 수 있습니다. 쿼리 힌트는 OPTION 절의 일부로 지정됩니다. 하나 이상의 쿼리 힌트로 인해 쿼리 최적화 프로그램에서 유효한 계획을 생성할 수 없는 경우 8622 오류가 발생합니다.

주의:
SQL Server 쿼리 최적화 프로그램은 일반적으로 쿼리에 대해 최적의 실행 계획을 선택하므로 힌트는 숙련된 개발자나 데이터베이스 관리자가 최후의 수단으로만 사용하는 것이 좋습니다.

적용 대상

DELETE

INSERT

SELECT

UPDATE

MERGE

항목 링크 아이콘 Transact-SQL 구문 표기 규칙

구문

<query_hint > ::= 
{ { HASH | ORDER } GROUP 
  | { CONCAT | HASH | MERGE } UNION 
  | { LOOP | MERGE | HASH } JOIN 
  | FAST number_rows 
  | FORCE ORDER 
  | MAXDOP number_of_processors 
  | OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
  | OPTIMIZE FOR UNKNOWN
  | PARAMETERIZATION { SIMPLE | FORCED }
  | RECOMPILE
  | ROBUST PLAN 
  | KEEP PLAN 
  | KEEPFIXED PLAN
  | EXPAND VIEWS 
  | MAXRECURSION number 
  | USE PLAN N'xml_plan'
    | TABLE HINT ( exposed_object_name  [ , <table_hint> [ [, ]...n ] ] )
<table_hint> ::=
[ NOEXPAND ] { 
    INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value )
  | FASTFIRSTROW 
  | FORCESEEK
  | HOLDLOCK 
  | NOLOCK 
  | NOWAIT
  | PAGLOCK 
  | READCOMMITTED 
  | READCOMMITTEDLOCK 
  | READPAST 
  | READUNCOMMITTED 
  | REPEATABLEREAD 
  | ROWLOCK 
  | SERIALIZABLE 
  | TABLOCK 
  | TABLOCKX 
  | UPDLOCK 
  | XLOCK 
}

인수

{ HASH | ORDER } GROUP

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

{ MERGE | HASH | CONCAT } UNION

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

{ LOOP | MERGE | HASH } JOIN

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

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

FAST number_rows

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

FORCE ORDER

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

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

쿼리에 뷰가 포함된 경우 SQL Server 쿼리 최적화 프로그램이 FORCE ORDER 힌트를 강제 적용하는 방법은 뷰 확인을 참조하십시오.

MAXDOP number

이 옵션을 지정한 쿼리에 대해서만 sp_configure의 max degree of parallelism 구성 옵션을 무시합니다. sp_configure로 구성한 값을 초과하면 MAXDOP 쿼리 힌트는 효과가 없습니다. MAXDOP 쿼리 힌트를 사용할 때 max degree of parallelism 구성 옵션에 사용된 모든 의미 체계 규칙은 적용할 수 있습니다. 자세한 내용은 max degree of parallelism 옵션을 참조하십시오.

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을 사용하면 쿼리 최적화 프로그램에서 이러한 문제가 발생할 수 있는 쿼리 계획을 고려하지 않도록 할 수 있습니다.

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

KEEP PLAN

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

KEEPFIXED PLAN

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

EXPAND VIEWS

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

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

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

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

MAXRECURSION number

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

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

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

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

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 ) | FASTFIRSTROW | FORCESEEK | HOLDLOCK | NOLOCK | NOWAIT | PAGLOCK | READCOMMITTED | READCOMMITTEDLOCK | READPAST | READUNCOMMITTED | REPEATABLEREAD | ROWLOCK | SERIALIZABLE | TABLOCK | TABLOCKX | UPDLOCK | XLOCK }

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

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

주의

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

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

매개 변수가 있는 쿼리와 함께 다시 컴파일을 사용한 성능 향상

SQL Server 2008 쿼리 최적화 프로그램은 RECOMPILE 쿼리 힌트를 사용하는 쿼리를 컴파일할 때 상수의 논리를 평가 및 단순화하여 쿼리 실행 계획의 효율성을 향상시키려고 합니다. 컴파일 시 실제 매개 변수 값이 알려져 쿼리 최적화 프로그램이 상수의 논리를 평가하기 전에 매개 변수를 상수로 대체할 수 있기 때문에 매개 변수가 있는 쿼리는 이러한 향상 내용을 활용할 수 있습니다.

예를 들어 다음 WHERE 절은 선택적 매개 변수가 많이 포함된 검색 쿼리의 일부입니다.

WHERE (o.OrderID = @orderid OR @orderid IS NULL)

AND (o.OrderDate >= @fromdate OR @fromdate IS NULL)

AND (o.OrderDate <= @todate OR @todate IS NULL)

AND (od.UnitPrice >= @minprice OR @minprice IS NULL)

AND (od.UnitPrice <= @maxprice OR @maxprice IS NULL)

AND (o.CustomerID = @custid OR @custid IS NULL)

AND (c.CompanyName LIKE @custname + '%' OR @custname IS NULL)

AND (c.City = @city OR @city IS NULL)

AND (c.Region = @region OR @region IS NULL)

AND (c.Country = @country OR @country IS NULL)

AND (od.ProductID = @prodid OR @prodid IS NULL)

AND (p.ProductName LIKE @prodname + '%' OR @prodname IS NULL)

쿼리가 RECOMPILE 쿼리 힌트를 사용하여 실행될 때 쿼리 최적화 프로그램은 각 매개 변수의 값에 따라 NULL 매개 변수가 있는 조건자를 제거하여 WHERE 절의 논리를 단순화합니다. 이 검색 쿼리가 @minprice = $5, @prodname = 'The'를 사용하여 호출되고 다른 모든 매개 변수가 NULL일 경우 쿼리 계획은 다음 조건자를 사용합니다.

WHERE od.UnitPrice >= $5 AND p.ProductName LIKE 'The%'.

WHERE 절이 간단할수록 쿼리 최적화 프로그램은 Index Scan 대신 Index Seek를 선택하는 것과 같이 보다 효율적인 쿼리 계획을 선택할 수 있습니다. 이전 버전의 SQL Server에서는 대부분의 매개 변수가 NULL인 경우에도 단순하지 않은 WHERE 절의 논리를 모두 평가하는 작업이 쿼리 계획에 포함되기 때문에 쿼리 최적화 프로그램이 이 쿼리에 대해 Index Scan을 선택할 가능성이 높습니다.

참고:
RECOMPILE 쿼리 힌트를 사용한 매개 변수 대체는 커서에 대한 DML 문에는 적용되지 않습니다.

이러한 성능 향상으로 인해 RECOMPILE 쿼리 힌트를 사용하는 쿼리가 이전 버전의 SQL Server에서는 성공적으로 컴파일되고 실행된 경우에도 SQL Server 2008에서는 실행 시 컴파일 오류가 발생할 수 있습니다. 예를 들어 다음 일괄 처리에는 매개 변수가 있는 쿼리를 사용하는 저장 프로시저가 포함되어 있습니다. 이전 버전의 SQL Server에서는 이 일괄 처리가 성공적으로 컴파일되고 실행됩니다. SQL Server 2008에서는 이 쿼리를 처음 호출하면 성공적으로 컴파일되지만 두 번째로 호출하면 오류 8622와 함께 컴파일이 실패합니다. 컴파일 오류가 발생하는 것은 쿼리 최적화 프로그램이 매개 변수를 상수로 대체한 다음 쿼리 논리를 단순화하여 HASH JOIN 힌트를 사용할 수 없기 때문입니다.

코드 복사
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAddressesByAddressIDs', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.uspGetAddressesByAddressIDs;
GO
-- Return a join of Person.Address and HumanResources.EmployeeAddress
-- on the AddressID column. If the two AddressID parameters are not 
-- equal, return a FULL JOIN. If the two AddressID parameters are equal,
-- return an INNER JOIN where Person.Address.AddressID and 
-- HumanResources.EmployeeAddress.AddressID equal the parameter values.
CREATE PROCEDURE HumanResources.uspGetAddressesByAddressIDs 
    @AddressID1 int, @AddressID2 int
AS
SELECT * FROM Person.Address JOIN HumanResources.EmployeeAddress
    ON ( (Person.Address.AddressID = HumanResources.EmployeeAddress.AddressID)
    AND ( Person.Address.AddressID=@AddressID1
    OR @AddressID1 != @AddressID2 ) )
OPTION (HASH JOIN, RECOMPILE);
GO
-- Parameters are not equal. Query compiles with no errors and 
-- returns all rows in the JOIN.
EXEC HumanResources.uspGetAddressesByAddressIDs 163,20;
GO
-- AddressID's do not match. Logic simplifies to finding
-- Person.Address.AddressID=163 and 
-- HumanResources.EmployeeAddress.AddressID=163.
-- The HASH JOIN query hint is no longer applicable and the query
-- gets error 8622 during compilation.
EXEC HumanResources.uspGetAddressesByAddressIDs 163,163;
GO

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

INDEX 또는 FORCESEEK 테이블 힌트는 계획 지침의 컨텍스트에서 쿼리 힌트로만 사용하는 것이 좋습니다. 계획 지침은 타사 응용 프로그램인 경우와 같이 원래 쿼리를 수정할 수 없을 때 유용합니다. 계획 지침에 지정되어 있는 쿼리 힌트는 쿼리가 컴파일 및 최적화되기 전에 쿼리에 추가됩니다. 임시 쿼리의 경우 계획 지침 문을 테스트할 때에만 TABLE HINT 절을 사용하십시오. 임시 쿼리 이외의 모든 경우 이러한 힌트를 테이블 힌트로만 지정하는 것이 좋습니다.

INDEX 및 FORCESEEK 테이블 힌트를 쿼리 힌트로 지정하는 경우 다음 개체에 대해서 유효합니다.

  • 테이블

  • 뷰

  • 인덱싱된 뷰

  • 공통 테이블 식(공통 테이블 식을 채울 결과 집합을 위한 SELECT 문에 힌트를 지정해야 함)

  • 동적 관리 뷰

  • 명명된 하위 쿼리

기존 테이블 힌트가 없는 쿼리에 대해 INDEX 및 FORCESEEK 테이블 힌트를 쿼리 힌트로 지정할 수 있습니다. 또는 INDEX 및 FORCESEEK 힌트를 사용하여 쿼리에 있는 기존 INDEX 또는 FORCESEEK 힌트를 각각 대체할 수 있습니다. 쿼리에 테이블 힌트를 지정하는 WITH 절이 없다면 INDEX 및 FORCESEEK 이외의 테이블 힌트를 쿼리 힌트로 사용할 수 없습니다. 이 경우 OPTION 절에 TABLE HINT를 사용하여 일치하는 힌트를 쿼리 힌트로 지정함으로써 쿼리의 의미 체계를 유지해야 합니다. 예를 들어 쿼리에 테이블 힌트 NOLOCK이 있는 경우 계획 지침의 @hints 매개 변수에 있는 OPTION 절에도 NOLOCK 힌트가 있어야 합니다. 예 11을 참조하십시오. 일치하는 쿼리 힌트 없이 OPTION 절에 TABLE HINT를 사용하여 INDEX 또는 FORCESEEK 이외의 테이블 힌트를 지정하거나 그 반대의 경우 OPTION 절로 인해 쿼리의 의미 체계가 변경되고 쿼리가 실패할 수 있음을 나타내는 오류 8702가 발생합니다. 자세한 내용은 계획 지침에서 INDEX 및 FORCESEEK 쿼리 힌트 사용을 참조하십시오.

예

1. MERGE JOIN 사용

다음 예에서는 쿼리의 JOIN 연산이 MERGE JOIN에 의해 수행되도록 지정합니다.

코드 복사
USE AdventureWorks;
GO
SELECT * 
FROM Sales.Customer AS c
INNER JOIN Sales.CustomerAddress AS ca ON c.CustomerID = ca.CustomerID
WHERE TerritoryID = 5
OPTION (MERGE JOIN);
GO

2. OPTIMIZE FOR 사용

다음 예에서는 쿼리를 최적화할 때 쿼리 최적화 프로그램이 지역 변수 @city_name에 'Seattle' 값을 사용하고 통계 데이터를 사용하여 지역 변수 @postal_code의 값을 결정하도록 지시합니다.

코드 복사
USE AdventureWorks;
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 사용

잘못 구성된 재귀 공통 테이블 식이 무한 루프에 진입하는 것을 방지하는 데 MAXRECURSION을 사용할 수 있습니다. 다음 예에서는 의도적으로 무한 루프를 만들고 MAXRECURSION 힌트를 사용하여 재귀 수준을 2로 제한하는 방법을 보여 줍니다.

코드 복사
USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

코딩 오류를 교정한 다음에는 더 이상 MAXRECURSION이 필요하지 않습니다.

4. MERGE UNION 사용

다음 예에서는 MERGE UNION 쿼리 힌트를 사용합니다.

코드 복사
USE AdventureWorks ;
GO
SELECT *
FROM HumanResources.Employee e1
UNION
SELECT *
FROM HumanResources.Employee e2
OPTION (MERGE UNION) ;
GO

5. HASH GROUP 및 FAST 사용

다음 예에서는 HASH GROUP 및 FAST 쿼리 힌트를 사용합니다.

코드 복사
USE AdventureWorks ;
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 사용

다음 예에서는 MAXDOP 쿼리 힌트를 사용합니다.

코드 복사
USE AdventureWorks ;
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 사용

다음은 INDEX 힌트를 사용하는 예입니다. 첫 번째는 단일 인덱스를 지정하는 예이고, 두 번째는 단일 테이블 참조에 대해 여러 인덱스를 지정하는 예입니다. 두 예에서 INDEX 힌트는 별칭을 사용하는 테이블에 적용되므로 TABLE HINT 절에서도 표시된 개체 이름과 동일한 별칭을 지정해야 합니다.

코드 복사
USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide1', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e, INDEX (IX_Employee_ManagerID)))';
GO
EXEC sp_create_plan_guide 
    @name = N'Guide2', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e, INDEX(PK_Employee_EmployeeID, IX_Employee_ManagerID)))';
GO

8. FORCESEEK 사용

다음은 FORCESEEK 테이블 힌트를 사용하는 예입니다. 이 예에서 INDEX 힌트는 두 부분으로 된 이름을 사용하는 테이블에 적용되므로 TABLE HINT 절에서도 표시된 개체 이름과 동일한 두 부분으로 된 이름을 지정해야 합니다.

코드 복사
USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide3', 
    @stmt = N'SELECT c.LastName, c.FirstName, HumanResources.Employee.Title
              FROM HumanResources.Employee
              JOIN Person.Contact AS c ON HumanResources.Employee.ContactID = c.ContactID
              WHERE HumanResources.Employee.ManagerID = 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. 여러 테이블 힌트 사용

다음 예에서는 한 테이블에 INDEX 힌트를 적용하고 다른 테이블에 FORCESEEK 힌트를 적용합니다.

코드 복사
USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide4', 
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_ManagerID ) ) 
                       , TABLE HINT ( c, FORCESEEK) )';
GO

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

다음은 힌트를 지정하지 않고 TABLE HINT 힌트를 사용하여 쿼리의 FROM 절에 지정된 INDEX 테이블의 동작을 다시 정의하는 방법을 보여 주는 예입니다.

코드 복사
USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide5', 
    @stmt = N'SELECT e.ManagerID, c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_ManagerID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT(e))';
GO

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

다음 예의 쿼리에는 두 가지 테이블 힌트가 포함되어 있습니다. 하나는 의미 체계에 영향을 주는 NOLOCK이고 다른 하나는 의미 체계에 영향을 주지 않는 INDEX입니다. 쿼리의 의미 체계를 유지하기 위해 계획 지침의 OPTIONS 절에 NOLOCK 힌트가 지정됩니다. NOLOCK 힌트 외에 INDEX 및 FORCESEEK 힌트가 지정되고 문을 컴파일 및 최적화할 때 쿼리에서 의미 체계에 영향을 주지 않는 INDEX 힌트를 대체합니다.

코드 복사
USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide6', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 3;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, INDEX( IX_Employee_ManagerID) , NOLOCK, FORCESEEK ))';
GO

다음 예에서는 최적화 프로그램에서 테이블 힌트에 지정된 인덱스 이외의 인덱스를 선택할 수 있도록 하면서 쿼리의 의미 체계를 유지하는 다른 방법을 보여 줍니다. 이 작업은 의미 체계에 영향을 주는 NOLOCK 힌트를 OPTIONS 절에 지정하고 테이블 참조만 있고 INDEX 힌트는 없는 TABLE HINT 키워드를 지정하여 수행됩니다.

코드 복사
USE AdventureWorks;
GO
EXEC sp_create_plan_guide 
    @name = N'Guide7', 
    @stmt = N'SELECT c.LastName, c.FirstName, e.Title
              FROM HumanResources.Employee AS e 
                   WITH (NOLOCK, INDEX (PK_Employee_EmployeeID))
              JOIN Person.Contact AS c ON e.ContactID = c.ContactID
              WHERE e.ManagerID = 2;',
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = N'OPTION (TABLE HINT ( e, NOLOCK))';
GO

참고 항목

참조

힌트(Transact-SQL)
sp_create_plan_guide(Transact-SQL)
sp_control_plan_guide(Transact-SQL)

관련 자료

계획 지침에서 INDEX 및 FORCESEEK 쿼리 힌트 사용

도움말 및 정보

SQL Server 2008 지원 받기
반응형
저작자표시

'연구개발 > DBA' 카테고리의 다른 글

같은 그룹의 데이터를 붙여서 출력하기  (0) 2009.07.29
Update Statistics undocumented 옵션  (0) 2009.07.29
CTE를 이용한 날짜 집합 이용하기  (0) 2009.07.29
OPENROWSET을 사용하여 큰 개체 가져오기  (0) 2009.07.29
.WRITE 절을 사용하여 큰 값 데이터 형식 업데이트하기  (0) 2009.07.29

+ Recent posts

반응형

티스토리툴바