sp_create_plan_guide(Transact-SQL)

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance

쿼리 힌트 또는 실제 쿼리 계획을 데이터베이스의 쿼리와 연결하기 위한 계획 가이드를 만듭니다. 계획 지침에 대한 자세한 내용은 Plan Guides를 참조하십시오.

Transact-SQL 구문 표기 규칙

구문

sp_create_plan_guide
    [ @name = ] N'name'
    [ , [ @stmt = ] N'stmt' ]
    , [ @type = ] { N'OBJECT' | N'SQL' | N'TEMPLATE' }
    [ , [ @module_or_batch = ] { N' [ schema_name. ] object_name' | N'batch_text' } ]
    [ , [ @params = ] N'@parameter_name data_type [ ,... n ]' ]
    [ , [ @hints = ] { N'OPTION ( query_hint [ , ...n ] )' | N'XML_showplan' } ]
[ ; ]

인수

[ @name = ] N'name'

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

[ @stmt = ] N'stmt'

계획 지침을 만들 Transact-SQL 문입니다. @stmt 기본값NULL인 nvarchar(max)입니다. SQL Server 쿼리 최적화 프로그램에서 @stmt 일치하는 쿼리를 인식하면 @name 적용됩니다. 계획 가이드를 만들려면 @stmt @type, @module_or_batch 및 @params 매개 변수로 지정된 컨텍스트에 표시되어야 합니다.

@stmt 쿼리 최적화 프로그램이 @module_or_batch 및 @params 식별된 일괄 처리 또는 모듈 내에서 제공된 해당 문과 일치하도록 허용하는 방식으로 제공해야 합니다. 자세한 내용은 설명 섹션을 참조하세요. @stmt 크기는 서버의 사용 가능한 메모리에 의해서만 제한됩니다.

[ @type = ] { N'OBJECT' | N'SQL' | N'TEMPLATE' }

@stmt 표시되는 엔터티의 형식입니다. @name 일치하는 @stmt 대한 컨텍스트를 지정합니다. @type nvarchar(60)이며 다음 값 중 하나일 수 있습니다.

• OBJECT

  현재 데이터베이스의 Transact-SQL 저장 프로시저, 스칼라 함수, 다중 상태 테이블 반환 함수 또는 Transact-SQL DML 트리거의 컨텍스트에 @stmt 표시됨을 나타냅니다.

• SQL

  @stmt 모든 메커니즘을 통해 SQL Server에 제출할 수 있는 독립 실행형 문 또는 일괄 처리의 컨텍스트에 표시됨을 나타냅니다. CLR(공용 언어 런타임) 개체 또는 확장 저장 프로시저 또는 사용EXEC N'<sql_string>'으로 제출된 Transact-SQL 문은 서버에서 일괄 처리로 처리되므로 @type SQL식별되어야 합니다. 지정된 경우 SQL @hints 매개 변수에 쿼리 힌트 PARAMETERIZATION { FORCED | SIMPLE } 를 지정할 수 없습니다.

• TEMPLATE

  계획 지침이 @stmt 표시된 양식에 매개 변수화되는 모든 쿼리에 적용됨을 나타냅니다. 지정된 경우 TEMPLATE 쿼리 힌트만 PARAMETERIZATION { FORCED | SIMPLE } @hints 매개 변수에 지정할 수 있습니다. 계획 지침에 대한 TEMPLATE 자세한 내용은 계획 지침을 사용하여 쿼리 매개 변수화 동작 지정을 참조하세요.

[ @module_or_batch = ] { N' [ schema_name. ] object_name' | N'batch_text' }

@stmt 표시되는 개체의 이름 또는 @stmt 나타나는 일괄 처리 텍스트를 지정합니다. @module_or_batch 기본값NULL인 nvarchar(max)입니다. 일괄 처리 텍스트에는 문을 포함 USE <database> 할 수 없습니다.

애플리케이션 에서 제출된 일괄 처리와 일치하도록 계획 가이드의 경우 SQL Server에 제출된 것과 동일한 형식의 문자로 @module_or_batch 제공해야 합니다. 이 일치 작업을 더 효과적으로 처리하기 위해 내부 변환은 수행되지 않습니다. 자세한 내용은 설명 섹션을 참조하세요.

[ <schema_name>. ] <object_name>는 Transact-SQL 저장 프로시저, 스칼라 함수, 다중 상태 테이블 반환 함수 또는 @stmt 포함하는 Transact-SQL DML 트리거의 이름을 지정합니다. 지정 <schema_name> 되지 않은 경우 <schema_name> 현재 사용자의 스키마를 사용합니다. 지정되고 @type SQL경우 NULL @module_or_batch 값은 @stmt 값으로 설정됩니다. @type TEMPLATE 경우 @module_or_batch 여야 NULL합니다.

[ @params = ] N'@parameter_name data_type [ ,... n ]'

@stmt 포함된 모든 매개 변수의 정의를 지정합니다. @params 기본값NULL인 nvarchar(max)입니다. @params 다음 옵션 중 하나가 true인 경우에만 적용됩니다.

• @type 또는 SQL TEMPLATE. 이면 TEMPLATE@params 이어야 NULL합니다.

• @stmt 사용하여 sp_executesql 제출되고 @params 매개 변수에 대한 값이 지정되거나 SQL Server에서 매개 변수화 후 내부적으로 문을 제출합니다. 데이터베이스 API(ODBC, OLE DB 및 ADO.NET 포함)에서 매개 변수가 있는 쿼리를 제출하면 API 서버 커서 루틴에 sp_executesql 대한 호출로 SQL Server에 나타납니다. 따라서 가이드나 TEMPLATE 계획 지침에서 SQL 일치시킬 수도 있습니다.

@params 매개 변수화 후 내부적으로 사용 sp_executesql 하거나 제출하여 SQL Server에 제출된 것과 정확히 동일한 형식으로 제공되어야 합니다. 자세한 내용은 설명 섹션을 참조하세요. 일괄 처리에 매개 변수 NULL 가 없는 경우 지정해야 합니다. @params 크기는 사용 가능한 서버 메모리에 의해서만 제한됩니다.

[ @hints = ] { N'OPTION ( query_hint [ , ... n ] )' | N'XML_showplan' }

@hints 기본값NULL인 nvarchar(max)입니다.

• OPTION ( <query_hint> [ , ...n ] )

  @stmt 일치하는 쿼리에 연결할 절을 지정 OPTION 합니다. @hints 문에 있는 SELECT 절과 구문상 동일 OPTION 해야 하며 유효한 쿼리 힌트 시퀀스를 포함할 수 있습니다.

• <XML_showplan>'

  힌트로 적용할 XML 형식의 쿼리 계획입니다.

  XML 실행 계획을 변수에 할당하는 것이 좋습니다. 그렇지 않으면 실행 계획에서 작은따옴표 앞에 다른 작은따옴표를 추가하여 작은따옴표를 이스케이프해야 합니다. 예제 E를 참조하세요.

• NULL

  쿼리 절에 지정된 기존 힌트가 OPTION 쿼리에 적용되지 않음을 나타냅니다. 자세한 내용은 OPTION 절을 참조하세요.

설명

표시되는 순서대로 인수 sp_create_plan_guide 를 제공해야 합니다. 매개 변수에 sp_create_plan_guide대한 값을 제공하는 경우 모든 매개 변수 이름을 명시적으로 지정하거나 전혀 지정하지 않아야 합니다. 예를 들어 @name =을 지정한 경우 @stmt =, @type = 등도 지정해야 합니다. 마찬가지로 생략되고 매개 변수 값만 제공된 경우 @name = 나머지 매개 변수 이름도 생략하고 해당 값만 제공되어야 합니다. 인수 이름은 구문을 이해하는 데 도움이 되는 설명적인 용도로만 사용됩니다. SQL Server는 지정된 매개 변수 이름이 이름이 사용되는 위치에 있는 매개 변수의 이름과 일치하는지 확인하지 않습니다.

동일한 쿼리 및 일괄 처리 또는 SQL 모듈에 대해 둘 OBJECT 이상의 또는 계획 가이드를 만들 수 있습니다. 그러나 언제든지 하나의 계획 지침만 사용하도록 설정할 수 있습니다.

절을 지정 WITH ENCRYPTION 하거나 임시인 저장 프로시저, 함수 또는 DML 트리거를 참조하는 @module_or_batch 값에 대한 형식 OBJECT 계획 지침을 만들 수 없습니다.

계획 지침에서 참조하는 함수, 저장 프로시저 또는 DML 트리거를 삭제하거나 수정하려고 하면 오류가 발생합니다. 계획 가이드에서 참조하는 트리거에 정의된 트리거인 테이블을 삭제하려고 하면 오류가 발생합니다.

계획 가이드는 SQL Server의 모든 버전에서 사용할 수 없습니다. SQL Server 버전에서 지원되는 기능 목록은 SQL Server 2022의 버전과 지원하는 기능을 참조하세요. 계획 지침은 모든 버전에서 볼 수 있습니다. 계획 지침이 포함된 데이터베이스를 모든 버전에 추가할 수 있습니다. 업그레이드된 버전의 SQL Server에 데이터베이스를 복원하거나 첨부해도 계획 지침은 그대로 유지됩니다. 서버 업그레이드를 수행한 후 각 데이터베이스에서 계획 지침의 바람직성을 확인해야 합니다.

계획 가이드 일치 요구 사항

쿼리의 @type 지정하거나 TEMPLATE 쿼리와 일치하도록 지정하는 계획 지침의 SQL 경우 @module_or_batch 및 @params 값 [, ... n ] 은 애플리케이션에서 제출한 것과 정확히 동일한 형식으로 제공되어야 합니다. 즉, SQL Server 컴파일러가 수신하는 대로 일괄 처리 텍스트를 정확하게 제공해야 합니다. 실제 일괄 처리 및 매개 변수 텍스트를 캡처하려면 SQL Server Profiler를 사용할 수 있습니다. 자세한 내용은 SQL Server Profiler를 사용하여 계획 가이드를 만들고 테스트하는 방법을 참조 하세요.

@type SQL @module_or_batch 설정된 NULL경우 @module_or_batch 값은 @stmt 값으로 설정됩니다. 즉, @stmt 값은 SQL Server에 제출된 것과 정확히 동일한 문자 형식으로 제공되어야 합니다. 이 일치 작업을 더 효과적으로 처리하기 위해 내부 변환은 수행되지 않습니다.

SQL Server가 @stmt 값과 일치하여 @module_or_batch @params 경우 [, ... n ], 또는 @type OBJECT경우 내부의 <object_name>해당 쿼리 텍스트에 대해 다음 문자열 요소는 고려되지 않습니다.

• 문자열 내부의 공백 문자(탭, 공백, 캐리지 리턴 또는 줄 바꿈)
• 주석(-- 또는 /* */)
• 후행 세미콜론

예를 들어 SQL Server는 @stmt 문자열 N'SELECT * FROM T WHERE a = 10' 을 다음 @module_or_batch 일치시킬 수 있습니다.

 N'SELECT *
 FROM T
 WHERE a = 10'

그러나 동일한 문자열은 이 @module_or_batch 일치하지 않습니다.

N'SELECT * FROM T WHERE b = 10'

SQL Server는 첫 번째 쿼리 내의 캐리지 리턴, 줄 바꿈 및 공백 문자를 무시합니다. 두 번째 쿼리에서 시퀀스는 WHERE b = 10 WHERE a = 10. 대/소문자를 구분하지 않는 키워드가 있는 경우를 제외하고 일치는 대/소문자를 구분하고 악센트를 구분합니다(데이터베이스의 데이터 정렬이 대/소문자를 구분하지 않는 경우에도). 일치는 빈 공간에 민감합니다. 일치 작업은 키워드의 축약 형식을 구분하지 않습니다. 예를 들어 키워드는 EXECUTEEXECexecute 동등한 것으로 간주됩니다.

계획 캐시에 대한 계획 지침 효과

모듈에 계획 지침을 만들면 해당 모듈에 대한 쿼리 계획이 계획 캐시에서 제거됩니다. 형식 OBJECT 또는 SQL 일괄 처리에 대한 계획 가이드를 만들면 동일한 해시 값인 일괄 처리에 대한 쿼리 계획이 제거됩니다. 형식 TEMPLATE 의 계획 가이드를 만들면 해당 데이터베이스 내의 계획 캐시에서 모든 단일 문 일괄 처리가 제거됩니다.

사용 권한

형식 OBJECT의 계획 지침을 만들려면 참조된 개체에 대한 권한이 필요합니다 ALTER . 형식 SQL 의 TEMPLATE계획 지침을 만들려면 현재 데이터베이스에 대한 권한이 필요합니다 ALTER .

예제

A. 저장 프로시저에서 쿼리에 대한 OBJECT 형식의 계획 가이드 만들기

다음 예에서는 애플리케이션 기반 저장 프로시저의 컨텍스트에서 실행된 쿼리와 일치하는 계획 지침을 만들고 해당 쿼리에 대해 OPTIMIZE FOR 힌트를 적용합니다.

저장 프로시저는 다음과 같습니다.

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO

CREATE PROCEDURE Sales.GetSalesOrderByCountry (
    @Country_region NVARCHAR(60)
)
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h
    INNER JOIN Sales.Customer AS c
        ON h.CustomerID = c.CustomerID
    INNER JOIN Sales.SalesTerritory AS t
        ON c.TerritoryID = t.TerritoryID
    WHERE t.CountryRegionCode = @Country_region;
END
GO

저장 프로시저의 쿼리에서 만든 계획 가이드는 다음과 같습니다.

EXEC sp_create_plan_guide
    @name = N'Guide1',
    @stmt = N'SELECT *
              FROM Sales.SalesOrderHeader AS h
              INNER JOIN Sales.Customer AS c
                 ON h.CustomerID = c.CustomerID
              INNER JOIN Sales.SalesTerritory AS t
                 ON c.TerritoryID = t.TerritoryID
              WHERE t.CountryRegionCode = @Country_region',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

B. 독립 실행형 쿼리에 대한 SQL 형식의 계획 가이드 만들기

다음 예제에서는 시스템 저장 프로시저를 사용하는 애플리케이션이 제출한 일괄 처리에서 쿼리를 일치시키는 계획 가이드를 sp_executesql 만듭니다.

일괄 처리는 다음과 같습니다.

SELECT TOP 1 *
FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC;

병렬 실행 계획이 이 쿼리에서 생성되지 않도록 하려면 다음 계획 지침을 만드십시오.

EXEC sp_create_plan_guide
    @name = N'Guide1',
    @stmt = N'SELECT TOP 1 *
              FROM Sales.SalesOrderHeader
              ORDER BY OrderDate DESC',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = N'OPTION (MAXDOP 1)';

C. 매개 변수가 있는 형식의 쿼리에 대한 TEMPLATE 형식의 계획 가이드 만들기

다음 예제에서는 지정된 형식으로 매개 변수화하는 쿼리와 일치하는 계획 지침을 만들고 SQL Server에 쿼리의 매개 변수화를 강제로 적용하도록 지시합니다. 다음 두 쿼리는 구문적으로 동일하지만 상수 리터럴 값에서만 다릅니다.

SELECT *
FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT *
FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;

매개 변수가 있는 쿼리 형식에 대한 계획 가이드는 다음과 같습니다.

EXEC sp_create_plan_guide
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

앞의 예에서 @stmt 매개 변수의 값은 매개 변수가 있는 쿼리 형식입니다. 이 값을 사용할 수 있는 sp_create_plan_guide 유일한 신뢰할 수 있는 방법은 sp_get_query_template 시스템 저장 프로시저를 사용하는 것입니다. 다음 스크립트는 매개 변수가 있는 쿼리를 가져온 다음 이에 대한 계획 지침을 만듭니다.

DECLARE @stmt NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);

EXEC sp_get_query_template N'SELECT * FROM AdventureWorks2022.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2022.Sales.SalesOrderDetail AS d
          ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;',
    @stmt OUTPUT,
    @params OUTPUT

EXEC sp_create_plan_guide N'TemplateGuide1',
    @stmt,
    N'TEMPLATE',
    NULL,
    @params,
    N'OPTION(PARAMETERIZATION FORCED)';

Important

@stmt 에 전달된 sp_get_query_template 매개 변수에 있는 상수 리터럴 값은 리터럴을 대체하는 매개 변수에 대해 선택한 데이터 형식에 영향을 미칠 수 있습니다. 이는 계획 지침 일치에 영향을 미칩니다. 서로 다른 매개 변수 값 범위를 처리하려면 둘 이상의 계획 지침을 만들어야 할 수 있습니다.

D. API 커서 요청을 사용하여 제출된 쿼리에 대한 계획 가이드 만들기

계획 가이드는 API 서버 커서 루틴에서 제출된 쿼리와 일치할 수 있습니다. 이러한 루틴에는 , sp_cursorprepexec및 sp_cursoropen.가 포함sp_cursorprepare됩니다. ADO, OLE DB 및 ODBC API를 사용하는 애플리케이션은 API 서버 커서를 사용하여 SQL Server와 자주 상호 작용합니다. 프로파일러 추적 이벤트를 확인하여 SQL Server Profiler 추적에서 API 서버 커서 루틴의 호출을 RPC:Starting 볼 수 있습니다.

계획 가이드를 RPC:Starting 사용하여 튜닝하려는 쿼리에 대한 프로파일러 추적 이벤트에 다음 데이터가 표시될 경우를 가정합니다.

DECLARE @p1 INT;
SET @p1 = - 1;

DECLARE @p2 INT;
SET @p2 = 0;

DECLARE @p5 INT;
SET @p5 = 4104;

DECLARE @p6 INT;
SET @p6 = 8193;

DECLARE @p7 INT;
SET @p7 = 0;

EXEC sp_cursorprepexec @p1 OUTPUT,
    @p2 OUTPUT,
    N'@P1 varchar(255),@P2 varchar(255)',
    N'SELECT * FROM Sales.SalesOrderHeader AS h INNER JOIN Sales.SalesOrderDetail AS d ON h.SalesOrderID = d.SalesOrderID WHERE h.OrderDate BETWEEN @P1 AND @P2',
    @p5 OUTPUT,
    @p6 OUTPUT,
    @p7 OUTPUT,
    '20040101',
    '20050101'

SELECT @p1, @p2, @p5, @p6, @p7;

호출 sp_cursorprepexec 에서 쿼리에 대한 SELECT 계획이 병합 조인을 사용하고 있지만 해시 조인을 사용하려고 합니다. 사용하여 sp_cursorprepexec 제출된 쿼리는 쿼리 문자열과 매개 변수 문자열을 포함하여 매개 변수화됩니다. 다음 계획 가이드를 만들어 쿼리 및 매개 변수 문자열을 호출할 때 문자 sp_cursorprepexec와 문자로 정확하게 사용하여 계획 선택을 변경할 수 있습니다.

EXEC sp_create_plan_guide
    @name = N'APICursorGuide',
    @stmt = N'SELECT * FROM Sales.SalesOrderHeader AS h
              INNER JOIN Sales.SalesOrderDetail AS d
                ON h.SalesOrderID = d.SalesOrderID
              WHERE h.OrderDate BETWEEN @P1 AND @P2',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(255),@P2 varchar(255)',
    @hints = N'OPTION(HASH JOIN)';

애플리케이션에서 이 쿼리의 후속 실행은 이 계획 가이드의 영향을 받으며, 해시 조인을 사용하여 쿼리를 처리합니다.

E. 캐시된 계획에서 XML 실행 계획을 가져와서 계획 가이드 만들기

다음 예제에서는 간단한 임시 SQL 문에 대한 계획 지침을 만듭니다. 이 문의 원하는 쿼리 계획은 매개 변수에서 쿼리에 대한 XML 실행 계획을 직접 지정하여 계획 가이드에 @hints 제공됩니다. 이 예제에서는 먼저 계획을 실행 SQL 하여 계획 캐시에 계획을 생성합니다. 이 예제에서는 생성된 계획이 원하는 계획이며 추가 쿼리 튜닝이 필요하지 않다고 가정합니다. 쿼리에 대한 XML 실행 계획은 , sys.dm_exec_sql_text및 sys.dm_exec_text_query_plan 동적 관리 뷰를 쿼리하여 sys.dm_exec_query_stats가져오고 변수에 @xml_showplan 할당됩니다. 그런 다음 @xml_showplan 변수가 @hints 매개 변수의 sp_create_plan_guide 문에 전달됩니다. 또는 sp_create_plan_guide_from_handle 저장 프로시저를 사용하여 계획 캐시의 쿼리 계획에서 계획 지침을 만들 수 있습니다.

USE AdventureWorks2022;
GO

SELECT City,
    StateProvinceID,
    PostalCode
FROM Person.Address
ORDER BY PostalCode DESC;
GO

DECLARE @xml_showplan NVARCHAR(MAX);

SET @xml_showplan = (
    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;%'
);

EXEC sp_create_plan_guide @name = N'Guide1_from_XML_showplan',
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = @xml_showplan;
GO

관련 콘텐츠

• 계획 지침
• sp_control_plan_guide(Transact-SQL)
• sys.plan_guides(Transact-SQL)
• 저장 프로시저 데이터베이스 엔진(Transact-SQL)
• 시스템 저장 프로시저(Transact-SQL)
• sys.dm_exec_sql_text(Transact-SQL)
• sys.dm_exec_cached_plans(Transact-SQL)
• sys.dm_exec_query_stats(Transact-SQL)
• sp_create_plan_guide_from_handle(Transact-SQL)
• sys.fn_validate_plan_guide(Transact-SQL)
• sp_get_query_template(Transact-SQL)