sp_create_plan_guide_from_handle(Transact-SQL)

적용 대상: SQL Server

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

Transact-SQL 구문 표기 규칙

구문

sp_create_plan_guide_from_handle
    [ @name = ] N'name'
    , [ @plan_handle = ] plan_handle
    [ , [ @statement_start_offset = ] statement_start_offset ]
[ ; ]

인수

[ @name = ] N'name'

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

[ @plan_handle = ] plan_handle

계획 캐시의 일괄 처리를 식별합니다. @plan_handle varbinary(64)이며 기본값은 없습니다. sys.dm_exec_query_stats 동적 관리 뷰에서 @plan_handle 가져올 수 있습니다.

[ @statement_start_offset = ] statement_start_offset

지정된 @plan_handle 일괄 처리 내에서 문의 시작 위치를 식별합니다. @statement_start_offset int이며 기본값은 .입니다NULL.

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

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

설명

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

Important

sp_create_plan_guide_from_handle 는 계획 캐시에 표시되는 계획에 따라 계획 지침을 만듭니다. 즉, 일괄 처리 텍스트, Transact-SQL 문 및 XML Showplan은 계획 캐시에서 결과 계획 가이드로 문자 단위(쿼리에 전달된 리터럴 값 포함)를 가져옵니다. 이러한 텍스트 문자열에는 데이터베이스의 메타데이터에 저장되는 중요한 정보가 포함될 수 있습니다. 적절한 권한이 있는 사용자는 SQL Server Management Studio의 sys.plan_guides 카탈로그 뷰 및 계획 지침 속성 대화 상자를 사용하여 이 정보를 볼 수 있습니다. 중요한 정보가 계획 가이드를 통해 공개되지 않도록 계획 캐시에서 만든 계획 지침을 검토하는 것이 좋습니다.

쿼리 계획 내에서 여러 문에 대한 계획 가이드 만들기

sp_create_plan_guide_from_handle 마찬가지로 sp_create_plan_guide대상 일괄 처리 또는 모듈에 대한 쿼리 계획을 계획 캐시에서 제거합니다. 이 작업은 모든 사용자가 새 계획 가이드를 사용하기 시작하도록 하기 위해 수행됩니다. 단일 쿼리 계획 내에서 여러 문에 대한 계획 지침을 만들 때 명시적 트랜잭션의 모든 계획 지침을 만들어 캐시에서 계획 제거를 연기할 수 있습니다. 이 메서드를 사용하면 트랜잭션이 완료되고 지정된 각 문에 대한 계획 지침이 생성될 때까지 계획을 캐시에 유지할 수 있습니다. 예제 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
INNER 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 형식임을 나타냅니다. Ad hoc 또는 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
INNER 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)