아티클
09/27/2010

계획 지침 디자인 및 구현

쿼리 텍스트를 직접 변경할 수 없거나 직접 변경하지 않으려는 경우 계획 지침을 사용하여 쿼리 성능을 최적화할 수 있습니다. 계획 지침은 쿼리 힌트나 정해진 쿼리 계획을 쿼리에 연결하여 쿼리 최적화에 영향을 미칩니다. 다음 컨텍스트에서 실행되는 쿼리와 일치하는 계획 지침을 만들 수 있습니다.

OBJECT 계획 지침은 Transact-SQL 저장 프로시저, 사용자 정의 스칼라 함수, 다중 문 사용자 정의 테이블 반환 함수 및 DML 트리거의 컨텍스트에서 실행되는 쿼리와 일치합니다.
SQL 계획 지침은 데이터베이스 개체의 일부가 아닌 독립 실행형 Transact-SQL 문과 일괄 처리의 컨텍스트에서 실행되는 쿼리와 일치합니다. SQL 기반 계획 지침은 지정된 형식으로 매개 변수화되는 쿼리와 일치되도록 하는 데도 사용될 수 있습니다.
TEMPLATE 계획 지침은 지정된 형식으로 매개 변수화되는 독립 실행형 쿼리와 일치합니다. 이러한 계획 지침은 쿼리 클래스에 대한 데이터베이스의 현재 PARAMETERIZATION 데이터베이스 SET 옵션을 재정의하는 데 사용됩니다.

자세한 내용은 계획 지침 이해를 참조하십시오.

만들 수 있는 총 계획 지침 수는 사용 가능한 시스템 리소스에 의해서만 제한됩니다. 그러나 계획 지침은 성능 향상이나 안정화를 목적으로 하는 중요 업무용 쿼리로 제한되어야 합니다. 배포된 응용 프로그램의 쿼리 로드 대부분에 영향을 주기 위해 계획 지침을 사용하면 안 됩니다.

새로운 릴리스의 SQL Server로 응용 프로그램을 업그레이드할 때는 계획 지침 정의를 다시 평가하고 테스트하는 것이 좋습니다. 성능 조정 요구 사항과 계획 지침 일치 동작은 변경될 수 있습니다. 잘못된 계획 지침으로 인해 쿼리가 실패하지는 않지만 이 경우 계획 지침을 사용하지 않은 채 계획이 컴파일됩니다. 데이터베이스를 SQL Server 2008로 업그레이드한 후에는 다음 태스크를 수행하여 sys.fn_validate_plan_guide 함수로 기존 계획 지침의 유효성을 검사하는 것이 좋습니다. 또는 SQL Server Profiler의 Plan Guide Unsuccessful 이벤트를 사용하여 잘못된 계획 지침이 있는지 모니터링할 수도 있습니다.

[!참고]

계획 지침은 SQL Server Standard, Developer, Evaluation 및 Enterprise 버전에서만 사용할 수 있지만 보기는 모든 버전에서 가능합니다. 계획 지침이 포함된 데이터베이스를 모든 버전에 추가할 수 있습니다. 업그레이드된 버전의 SQL Server 2008에 데이터베이스를 복원하거나 첨부해도 계획 지침은 그대로 유지됩니다.

계획 지침에 쿼리 힌트 연결

계획 지침에 모든 올바른 쿼리 힌트 조합을 사용할 수 있습니다. 계획 지침이 쿼리와 일치하면 컴파일 및 최적화하기 전에 계획 지침의 힌트 절에 지정된 OPTION 절이 쿼리에 추가됩니다. 계획 지침에 일치하는 쿼리에 이미 OPTION 절이 있으면 계획 지침에 지정된 쿼리 힌트가 쿼리에서 이를 대체합니다. 그러나 이미 OPTION 절이 있는 쿼리에 계획 지침을 일치시키려면 해당 쿼리 텍스트를 지정할 때 sp_create_plan_guide 문이 쿼리의 OPTION 절을 포함해야 합니다. 계획 지침에 지정한 힌트로 이미 쿼리에 있는 힌트를 대체하는 대신 계획 지침에 지정한 힌트를 이미 쿼리에 있는 힌트에 추가하려면 원래 힌트와 추가 힌트를 모두 계획 지침의 OPTION 절에 지정해야 합니다.

주의
쿼리 힌트를 오용하는 계획 지침을 사용하면 컴파일, 실행 또는 성능 문제가 발생할 수 있습니다. 계획 지침은 숙련된 개발자와 데이터베이스 관리자만 사용해야 합니다.

계획 지침에 사용되는 일반적인 쿼리 힌트

계획 지침을 사용할 경우 도움이 되는 쿼리는 일반적으로 매개 변수 기반이며 매개 변수 값이 최악의 시나리오나 가장 대표적인 시나리오를 나타내지 않는 캐시된 쿼리 계획을 사용하므로 성능이 좋지 않을 수 있습니다. OPTIMIZE FOR 및 RECOMPILE 쿼리 힌트를 사용하면 이러한 문제를 해결할 수 있습니다. OPTIMIZE FOR는 쿼리가 최적화될 때 매개 변수에 특정 값을 사용하도록 SQL Server에 지시합니다. RECOMPILE은 실행 후 쿼리 계획을 삭제하도록 서버에 지시하여 다음 번에 같은 쿼리를 실행할 때 쿼리 최적화 프로그램이 새로운 쿼리 계획을 다시 컴파일하도록 합니다. 예를 보려면 계획 지침 이해를 참조하십시오.

또한 INDEX 및 FORCESEEK 테이블 힌트를 쿼리 힌트로 지정할 수도 있습니다. 쿼리 힌트로 지정할 경우 이러한 힌트는 인라인 테이블이나 뷰 힌트처럼 동작합니다. INDEX 힌트는 쿼리 최적화 프로그램이 지정한 인덱스만 사용하여 참조된 테이블이나 뷰에 있는 데이터에 액세스하도록 합니다. FORCESEEK 힌트는 최적화 프로그램이 Index Seek 연산만 사용하여 참조된 테이블이나 뷰에 있는 데이터에 액세스하도록 합니다. 이러한 힌트는 추가 계획 지침 기능을 제공하며 계획 지침을 사용하는 쿼리의 최적화에 보다 많은 영향을 줄 수 있습니다. 예를 보려면 계획 지침에서 INDEX 및 FORCESEEK 쿼리 힌트 사용을 참조하십시오.

계획 지침에 쿼리 계획 연결

정해진 쿼리 계획을 적용하는 계획 지침은 최적화 프로그램에서 특정 쿼리에 대해 선택한 실행 계획보다 더 뛰어난 기존 실행 계획을 알고 있는 경우 유용합니다. 쿼리에 정해진 계획을 적용하면 쿼리 최적화 프로그램에서 더 이상 통계 및 인덱스 변경 내용에 쿼리에 대한 계획을 적용할 수 없습니다. 정해진 쿼리 계획을 사용하는 계획 지침을 고려할 때는 정해진 계획을 적용하는 경우의 이점과 데이터 배포 및 사용 가능한 인덱스 변경 내용으로 계획을 자동 적용할 수 없는 경우의 이점을 비교해 보십시오.

sp_create_plan_guide 문의 xml_showplan 매개 변수에 계획의 XML 실행 계획을 지정하거나 sp_create_plan_guide_from_handle 문에 캐시된 계획의 계획 핸들을 지정하여 계획 지침에 특정 쿼리 계획을 연결할 수 있습니다. 두 방법 모두 대상 쿼리에 정해진 쿼리 계획을 적용합니다.

계획 지침 일치 요구 사항

계획 지침의 범위는 이 지침이 생성되는 데이터베이스입니다. 따라서 쿼리를 실행할 때 현재 데이터베이스에 있는 계획 지침만 쿼리에 일치시킬 수 있습니다. 예를 들어 AdventureWorks가 현재 데이터베이스이면 다음 쿼리가 실행됩니다.

SELECT * FROM Person.Contact;

AdventureWorks 데이터베이스의 계획 지침만 이 쿼리에 일치될 수 있습니다.

그러나 AdventureWorks가 현재 데이터베이스이면 다음 문이 실행됩니다.

USE DB1;

SELECT * FROM Person.Contact;

쿼리가 DB1 컨텍스트에서 실행되므로 DB1의 계획 지침만 쿼리에 일치됩니다.

SQL 또는 TEMPLATE 기반 계획 지침을 위해 SQL Server는 두 값의 문자를 비교하여 @module\_or\_batch 및 @params 인수의 값을 일치시킵니다. 따라서 SQL Server가 실제 일괄 처리에서 수신한 것과 똑같이 텍스트를 제공해야 합니다.

@type = 'SQL' 및 @module\_or\_batch가 NULL로 설정되면 @module\_or\_batch 값이 @stmt 값으로 설정됩니다. 이것은 statement_text의 값이 SQL Server에 전송된 것과 문자 수준까지 같은 형식으로 제공되어야 함을 의미합니다. 이 일치 작업을 더 효과적으로 처리하기 위해 내부 변환은 수행되지 않습니다.

일반적으로 SQL Server Profiler를 사용해 쿼리가 계획 지침에 맞는지 확인하여 계획 지침을 테스트할 수 있습니다. SQL Server Management Studio에서 일괄 처리를 실행하여 SQL 또는 TEMPLATE 기반 계획 지침을 테스트하면 예기치 않은 결과를 얻을 수도 있습니다. 자세한 내용은 SQL Server 프로파일러를 사용하여 계획 지침 작성 및 테스트를 참조하십시오.

[!참고]

계획 지침을 만들려는 문이 포함된 일괄 처리는 USE database 문을 포함할 수 없습니다.

계획 캐시의 계획 지침 효과

모듈에 대한 계획 지침을 만들면 계획 캐시에서 해당 모듈에 대한 쿼리 계획이 제거되고, 일괄 처리에 OBJECT 또는 SQL 유형의 계획 지침을 만들면 같은 해시 값을 가진 일괄 처리에 대한 쿼리 계획이 제거되며, TEMPLATE 유형의 계획 지침을 만들면 해당 데이터베이스 내의 계획 캐시에서 단일 문 일괄 처리가 모두 제거됩니다.