쿼리 계획 이동

대부분의 경우 데이터베이스를 최신 버전의 SQL Server로 업그레이드하면 쿼리 성능이 향상됩니다. 그러나 성능에 맞게 신중하게 조정된 중요 업무용 쿼리가 있는 경우 각 쿼리에 대한 계획 지침을 만들어 업그레이드하기 전에 이러한 쿼리에 대한 쿼리 계획을 유지할 수 있습니다. 업그레이드 후 쿼리 최적화 프로그램이 하나 이상의 쿼리에 대해 덜 효율적인 계획을 선택하는 경우 계획 지침을 사용하도록 설정하고 쿼리 최적화 프로그램에서 업그레이드 전 계획을 사용하도록 강제 적용할 수 있습니다.

업그레이드하기 전에 계획 지침을 만들려면 다음 단계를 수행합니다.

sp_create_plan_guide 저장 프로시저를 사용하고 USE PLAN 쿼리 힌트에서 쿼리 계획을 지정하여 각 중요 업무용 쿼리에 대한 현재 계획을 기록합니다.
계획 지침이 쿼리에 적용되는지 확인합니다.
데이터베이스를 최신 버전의 SQL Server로 업그레이드합니다.

계획은 계획 지침의 업그레이드된 데이터베이스에 유지되며 업그레이드 후 계획 회귀의 경우 대체 역할을 합니다.

업데이트된 통계로 인해 새 릴리스에서 더 나은 계획이나 유익한 다시 컴파일 기회를 놓칠 수 있으므로 업그레이드 후에 계획 지침을 사용하도록 설정하지 않는 것이 좋습니다.
업그레이드 후에 덜 효율적인 계획을 선택하는 경우 계획 가이드의 전체 또는 하위 집합을 활성화하여 새 계획을 재정의합니다.

예시

다음 예제에서는 계획 가이드를 만들어 쿼리에 대한 업그레이드 전 계획을 기록하는 방법을 보여줍니다.

1단계: 계획 수집

계획 지침에 기록된 쿼리 계획은 XML 형식이어야 합니다. XML 형식 쿼리 계획은 다음과 같은 방법으로 생성할 수 있습니다.

SET SHOWPLAN_XML (실행 계획 XML을 설정합니다)
SET STATISTICS XML
sys.dm_exec_query_plan 동적 관리 함수의 query_plan 열을 쿼리합니다.
SQL Server Profiler Showplan XML, Showplan XML Statistics Profile 및 Showplan XML for Query Compile 이벤트 클래스입니다.

다음 예제에서는 동적 관리 뷰를 쿼리하여 문 SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;의 쿼리 계획을 수집합니다.

USE AdventureWorks;  
GO  
SELECT query_plan  
    FROM sys.dm_exec_query_stats AS qs   
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp  
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%';  
GO

2단계: 계획을 강제 적용하기 위한 계획 가이드 만들기

계획 가이드에서 XML 형식 쿼리 계획(이전에 설명한 방법 중 하나로 얻은)을 사용하여 쿼리 계획을 복사하여 sp_create_plan_guide OPTION 절에 지정된 USE PLAN 쿼리 힌트 내에 문자열 리터럴로 붙여넣습니다.

XML 계획 자체 내에서 계획 지침을 만들기 전에 두 번째 따옴표를 사용하여 계획에 나타나는 따옴표(')를 이스케이프합니다. 예를 들어, WHERE A.varchar = 'This is a string'를 포함한 계획은 코드를 WHERE A.varchar = ''This is a string''로 수정하여 이스케이프해야 합니다.

다음 예제에서는 1단계에서 수집된 쿼리 계획에 대한 계획 지침을 만들고 매개 변수에 쿼리에 대한 XML Showplan을 @hints 삽입합니다. 간단히 하기 위해 예제에는 부분적인 실행 계획 출력만 포함됩니다.

EXECUTE sp_create_plan_guide   
@name = N'Guide1',  
@stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',  
@type = N'SQL',  
@module_or_batch = NULL,  
@params = NULL,  
@hints = N'OPTION(USE PLAN N''<ShowPlanXML xmlns=''''https://schemas.microsoft.com/sqlserver/2004/07/showplan''''   
    Version=''''0.5'''' Build=''''9.00.1116''''>  
    <BatchSequence><Batch><Statements><StmtSimple>  
    ...  
    </StmtSimple></Statements></Batch>  
    </BatchSequence></ShowPlanXML>'')';  
GO

3단계: 계획 지침이 쿼리에 적용되는지 확인

쿼리를 다시 실행하고 생성된 쿼리 계획을 검사합니다. 계획이 계획 가이드에서 지정한 계획과 일치하는 것을 볼 수 있습니다.

또한 참조하십시오

sp_create_plan_guide(Transact-SQL)
쿼리 힌트(Transact SQL)
계획 지침

Last updated on 2017-03-06