sp_create_plan_guide_from_handle(Transact-SQL)

적용 대상:SQL Server

계획 캐시의 쿼리 계획에서 하나 이상의 계획 지침을 만듭니다. 이 저장 프로시저를 사용하여 쿼리 최적화 프로그램이 항상 지정한 쿼리에 특정 쿼리 계획을 사용하도록 할 수 있습니다. 계획 지침에 대한 자세한 내용은 Plan Guides를 참조하십시오.

Transact-SQL 구문 표기 규칙

구문

sp_create_plan_guide_from_handle [ @name = ] N'plan_guide_name'  
    , [ @plan_handle = ] plan_handle  
    , [ [ @statement_start_offset = ] { statement_start_offset | NULL } ]  

인수

[ @name = ] N'plan_guide_name'
계획 가이드의 이름입니다. 계획 가이드 이름은 현재 데이터베이스로 범위가 지정됩니다. plan_guide_name 식별자에 대한 규칙을 준수해야 하며 숫자 기호(#)로 시작할 수 없습니다. plan_guide_name 최대 길이는 124자입니다.

[ @plan_handle = ] plan_handle
계획 캐시의 일괄 처리를 식별합니다. plan_handle은 varbinary(64)입니다. plan_handle sys.dm_exec_query_stats 동적 관리 뷰에서 가져올 수 있습니다.

[ @statement_start_offset = ] { statement_start_offset | NULL } ]
지정된 plan_handle 일괄 처리 내에서 문의 시작 위치를 식별합니다. statement_start_offset 기본값이 NULL인 int입니다.

문 오프셋은 sys.dm_exec_query_stats 동적 관리 뷰의 statement_start_offset 열에 해당합니다.

NULL을 지정하거나 문 오프셋을 지정하지 않으면 지정된 계획 핸들에 대한 쿼리 계획을 사용하여 일괄 처리의 각 문에 대한 계획 가이드가 만들어집니다. 결과 계획 가이드는 USE PLAN 쿼리 힌트를 사용하여 특정 계획을 강제로 사용하는 계획 지침과 동일합니다.

설명

모든 문 형식에 대한 계획 지침을 만들 수 없습니다. 일괄 처리의 문에 대한 계획 지침을 만들 수 없는 경우 저장 프로시저는 문을 무시하고 일괄 처리의 다음 문을 계속 진행합니다. 문이 동일한 일괄 처리에서 여러 번 발생하는 경우 마지막 발생에 대한 계획이 활성화되고 문에 대한 이전 계획이 비활성화됩니다. 일괄 처리의 문을 계획 지침에 사용할 수 없는 경우 오류 10532가 발생하고 문이 실패합니다. 이 오류의 가능성을 방지하려면 항상 sys.dm_exec_query_stats 동적 관리 보기에서 계획 핸들을 가져오는 것이 좋습니다.

쿼리 힌트 내에 여러 명령문에 대한 계획 지침 만들기

sp_create_plan_guide 마찬가지로 sp_create_plan_guide_from_handle 계획 캐시에서 대상 일괄 처리 또는 모듈에 대한 쿼리 계획을 제거합니다. 이 작업은 모든 사용자가 새 계획 가이드를 사용하기 시작하도록 하기 위해 수행됩니다. 단일 쿼리 계획 내에서 여러 문에 대한 계획 지침을 만들 때 명시적 트랜잭션의 모든 계획 지침을 만들어 캐시에서 계획 제거를 연기할 수 있습니다. 이 메서드를 사용하면 트랜잭션이 완료되고 지정된 각 문에 대한 계획 지침이 생성될 때까지 계획을 캐시에 유지할 수 있습니다. 예제 B를 참조하세요.

사용 권한

VIEW SERVER STATE 권한이 필요합니다. 또한 sp_create_plan_guide_from_handle 사용하여 만든 각 계획 가이드에 대해 개별 권한이 필요합니다. OBJECT 형식의 계획 지침을 만들려면 참조된 개체에 대한 권한이 필요합니다 ALTER . SQL 또는 TEMPLATE 형식의 계획 지침을 만들려면 현재 데이터베이스에 대한 권한이 필요합니다 ALTER . 만들 계획 지침 유형을 확인하려면 다음 쿼리를 실행합니다.

SELECT cp.plan_handle, sql_handle, st.text, objtype   
FROM sys.dm_exec_cached_plans AS cp  
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st;  

계획 지침을 만드는 문이 포함된 행에서 결과 집합의 objtype 열을 검사합니다. 값 Proc 은 계획 지침이 OBJECT 형식임을 나타냅니다. AdHoc 또는 Prepared와 같은 다른 값은 결과 지침이 SQL 유형임을 나타냅니다.

예

A. 계획 캐시의 쿼리 계획에서 계획 가이드 만들기

다음 예제에서는 계획 캐시에서 쿼리 계획을 지정하여 단일 SELECT 문에 대한 계획 지침을 만듭니다. 이 예제는 계획 가이드를 만들 간단한 SELECT 문을 실행하여 시작합니다. 이 쿼리의 계획은 sys.dm_exec_sql_text 및 sys.dm_exec_text_query_plan 동적 관리 뷰를 사용하여 검사됩니다. 그런 다음, 쿼리와 연결된 계획 캐시에 쿼리 계획을 지정하여 쿼리에 대한 계획 가이드를 만듭니다. 예제의 최종 문은 계획 지침이 있는지 확인합니다.

USE AdventureWorks2022;  
GO  
SELECT WorkOrderID, p.Name, OrderQty, DueDate  
FROM Production.WorkOrder AS w   
JOIN Production.Product AS p ON w.ProductID = p.ProductID  
WHERE p.ProductSubcategoryID > 4  
ORDER BY p.Name, DueDate;  
GO  
-- Inspect the query plan by using dynamic management views.  
SELECT * FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(sql_handle)  
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp  
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';  
GO  
-- Create a plan guide for the query by specifying the query plan in the plan cache.  
DECLARE @plan_handle varbinary(64);  
DECLARE @offset int;  
SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset  
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st  
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp  
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';  
  
EXECUTE sp_create_plan_guide_from_handle   
    @name =  N'Guide1',  
    @plan_handle = @plan_handle,  
    @statement_start_offset = @offset;  
GO  
-- Verify that the plan guide is created.  
SELECT * FROM sys.plan_guides  
WHERE scope_batch LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';  
GO  

B. 다중 상태 일괄 처리에 대한 여러 계획 가이드 만들기

다음 예제에서는 다중 상태 일괄 처리 내에서 두 문에 대한 계획 지침을 만듭니다. 계획 지침은 명시적 트랜잭션 내에서 만들어지므로 일괄 처리에 대한 쿼리 계획은 첫 번째 계획 지침을 만든 후에 계획 캐시에서 제거되지 않습니다. 이 예에서는 다중 문 일괄 처리를 실행하여 시작합니다. 일괄 처리에 대한 계획은 동적 관리 뷰를 사용하여 검사됩니다. 일괄 처리의 각 문에 대한 행이 반환됩니다. 그런 다음 @statement_start_offset 매개 변수를 지정하여 일괄 처리의 첫 번째 문과 세 번째 문에 대한 계획 지침을 만듭니다. 예제의 최종 문은 계획 지침이 있는지 확인합니다.

USE AdventureWorks2022;
GO
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4;
SELECT * FROM Person.Address;
SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10;
GO

-- Examine the query plans for this batch
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%';
GO

-- Create plan guides for the first and third statements in the batch by specifying the statement offsets.
BEGIN TRANSACTION

DECLARE @plan_handle varbinary(64);
DECLARE @offset int;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                        ((CASE statement_end_offset 
                              WHEN -1 THEN DATALENGTH(st.text)
                              ELSE qs.statement_end_offset END 
                              - qs.statement_start_offset)/2) + 1)  like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide_Statement1_only',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 4%'
AND SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                        ((CASE statement_end_offset 
                              WHEN -1 THEN DATALENGTH(st.text)
                              ELSE qs.statement_end_offset END 
                              - qs.statement_start_offset)/2) + 1)  like 'SELECT * FROM Production.Product WHERE ProductSubcategoryID > 10%'

EXECUTE sp_create_plan_guide_from_handle 
    @name =  N'Guide_Statement3_only',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;

COMMIT TRANSACTION
GO

-- Verify the plan guides are created.
SELECT * FROM sys.plan_guides;
GO

참고 항목

데이터베이스 엔진 저장 프로시저(Transact-SQL)
sys.dm_exec_query_stats(Transact-SQL)
계획 지침
sp_create_plan_guide(Transact-SQL)
sys.dm_exec_sql_text(Transact-SQL)
sys.dm_exec_text_query_plan(Transact-SQL)
sp_control_plan_guide(Transact-SQL)