sp_create_plan_guide(Transact-SQL)

아티클
12/15/2008

업데이트: 2007년 9월 15일

쿼리 힌트를 데이터베이스의 쿼리와 연결하기 위한 계획 지침을 만듭니다. 계획 지침에 대한 자세한 내용은 계획 지침을 사용하여 배포된 응용 프로그램의 쿼리 최적화를 참조하십시오.

구문

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

인수

[ @name= ] N'plan_guide_name'
계획 지침을 식별하는 이름을 지정합니다. 계획 지침 이름은 현재 데이터베이스 범위에 적용됩니다. plan_guide_name은 식별자에 대한 규칙을 따라야 하며 숫자 기호(#)로 시작할 수 없습니다.

[ @stmt= ] N'statement_text'
계획 지침을 만들 Transact-SQL 문입니다. SQL Server 쿼리 최적화 프로그램이 statement_text와 일치하는 쿼리를 인식할 때 plan_guide_name이 적용됩니다. 계획 지침을 성공적으로 만들려면 statement_text는 @type, @module_or_batch 및 @params 매개 변수에 의해 지정된 컨텍스트에 있어야 합니다.

statement_text는 SQL Server에서 @module_or_batch 및 @params에 의해 식별된 일괄 처리나 모듈 안에 제공되는 해당 문과 일치하도록 하는 방법으로 제공되어야 합니다. statement_text 가 표준 내부 형식으로 변환된 후에 SQL Server는 이러한 일치 작업(공백, 주석 및 키워드의 대/소문자는 구분하지 않음)을 시도합니다. 자세한 내용은 주의 섹션을 참조하십시오. statement_text의 크기는 서버의 사용 가능한 메모리 크기로만 제한됩니다.

[ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
statement_text가 표시되는 엔터티 유형입니다. statement_text 를 plan_guide_name와 일치시키기 위한 컨텍스트를 제공합니다.
- OBJECT
  statement_text 가 현재 데이터베이스에 있는 Transact-SQL 저장 프로시저, 스칼라 함수, 다중 문 테이블 값 함수 또는 Transact-SQL DML 트리거의 컨텍스트에 있음을 나타냅니다.
- SQL
  statement_text가 모든 메커니즘을 통해 SQL Server로 전송할 수 있는 독립 실행형 문 또는 일괄 처리의 컨텍스트에 있음을 나타냅니다. CLR(공용 언어 런타임) 개체 또는 확장 저장 프로시저에 의해 전송되거나 EXEC **N'sql_string'**을 사용하여 전송된 Transact-SQL 문은 서버에서 일괄적으로 처리되므로 **@type ='SQL'**로 지정되어야 합니다. SQL을 지정한 경우 @hints 매개 변수에 PARAMETERIZATION { FORCED | SIMPLE } 쿼리 힌트를 지정할 수 없습니다.
- TEMPLATE
  계획 지침이 statement_text에 지정된 형식으로 매개 변수화되는 모든 쿼리에 대해 적용됨을 나타냅니다. TEMPLATE가 지정된 경우 PARAMETERIZATION { FORCED | SIMPLE } 쿼리 힌트만 @hints 매개 변수에 지정될 수 있습니다. TEMPLATE 계획 지침에 대한 자세한 내용은 계획 지침을 사용하여 쿼리 매개 변수화 동작 지정을 참조하십시오.

[ @module_or_batch = ] { N'[ schema_name**.** ] object_name**'** | N'batch_text' | NULL }
statement_text가 있는 개체의 이름 또는 statement_text가 있는 일괄 처리 텍스트를 지정합니다. 일괄 처리 텍스트는 USE database 문을 포함할 수 없습니다.

계획 지침을 응용 프로그램으로부터 전송된 일괄 처리와 일치시키려면 batch_tex는 SQL Server에 전송된 것과 문자 수준까지 같은 형식으로 제공되어야 합니다. 이 일치 작업을 더 효과적으로 처리하기 위해 내부 변환은 수행되지 않습니다. 자세한 내용은 주의 섹션을 참조하십시오.

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

[ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
statement_text에 포함된 모든 매개 변수의 정의를 지정합니다. @params는 다음 중 하나에 해당하는 경우에만 적용됩니다.
- @type='SQL' 또는 'TEMPLATE'. **'TEMPLATE'**인 경우 @params는 NULL일 수 없습니다.
- statement_text는 sp_executesql 및 지정된 @params 매개 변수 값을 사용하여 전송되거나 SQL Server가 문을 매개 변수화한 후에 내부에서 전송합니다. 데이터베이스 API(ODBC, OLE DB, ADO.NET 등)에서 매개 변수가 있는 쿼리의 전송은 SQL Server에 sp_executesql 또는 API 서버 커서 루틴에 대한 호출로 나타나므로 SQL 또는 TEMPLATE 계획 지침으로 일치시킬 수도 있습니다. 매개 변수화 및 계획 지침에 대한 자세한 내용은 SQL Server에서 계획 지침을 쿼리에 대응시키는 방법을 참조하십시오.
@parameter_name data_type 은 sp_executesql을 사용하여 SQL Server에 전송하거나 매개 변수화한 후 내부에서 전송한 것과 정확히 같은 형식으로 제공되어야 합니다. 자세한 내용은 주의 섹션을 참조하십시오. 일괄 처리에 매개 변수가 없는 경우 NULL이 지정되어야 합니다. @params의 크기는 서버의 사용 가능한 메모리 크기로만 제한됩니다.

[@hints = ] { **N'**OPTION **(**query_hint [ ,...n ] )' | NULL }
@stmt와 일치하는 쿼리에 연결할 OPTION 절을 지정합니다. @hints는 SELECT 문의 OPTION 절과 구문이 같아야 하며 쿼리 힌트의 유효한 시퀀스를 포함할 수 있습니다. NULL은 OPTION 절이 없음을 나타냅니다. 자세한 내용은 OPTION 절(Transact-SQL)을 참조하십시오.

주의

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

특정 @module_or_batch 및 @stmt 조합에 대해 하나의 계획 지침만을 만들 수 있습니다.

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

활성화 여부에 관계없이 계획 지침에서 참조하는 함수, 저장 프로시저 또는 DML 트리거를 삭제하거나 수정하려고 하면 오류가 발생합니다. 계획 지침에서 참조하는 트리거가 정의되어 있는 테이블을 삭제하려는 경우에도 오류가 발생합니다.

[!참고] 계획 지침은 SQL Server Standard, Developer, Evaluation 및 Enterprise Edition에서만 사용할 수 있지만 보는 것은 어떤 버전에서도 가능합니다. 계획 지침이 포함된 데이터베이스를 모든 버전에 추가할 수 있습니다. 업그레이드된 버전의 SQL Server 2008에 데이터베이스를 복원하거나 첨부해도 계획 지침은 그대로 유지됩니다. 서버를 업그레이드한 후에는 각 데이터베이스의 계획 지침을 원활하게 사용할 수 있는지 확인해야 합니다.

계획 지침 일치 요구 사항

@type='SQL' 또는 @type='TEMPLATE'을 지정하는 계획 지침이 성공적으로 쿼리와 일치하게 하려면 batch_text 및 @parameter_name data_type [,...n ]의 값이 응용 프로그램에 의해 전송된 해당 부분과 정확히 같은 형식으로 제공되어야 합니다. 이는 SQL Server 컴파일러가 수신한 것과 정확히 같게 일괄 처리 텍스트를 제공해야 함을 의미합니다. 실제 일괄 처리 및 매개 변수 텍스트를 캡처하기 위해 SQL Server 프로파일러를 사용할 수 있습니다. 자세한 내용은 SQL Server 프로파일러를 사용하여 계획 지침 작성 및 테스트를 참조하십시오.

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

SQL Server에서 statement_text 값을 batch_text 및 @parameter_name data_type [,...n ]에 일치시키거나, **@type='OBJECT'**인 경우, object_name 내의 해당되는 쿼리 텍스트에 일치시킬 때는 다음 문자열 요소가 무시됩니다.

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

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

N'SELECT *

FROM T

WHERE a=10'

그러나 같은 문자열이 다음 batch_text와는 일치되지 않습니다.

N'SELECT * FROM T WHERE b = 10'

SQL Server는 첫째 쿼리 안에 있는 캐리지 리턴, 줄 바꿈 및 공백 문자를 무시합니다. 둘째 쿼리에서 WHERE b = 10 시퀀스는 WHERE a = 10과 다르게 해석됩니다. 일치 작업은 대/소문자를 구분하지 않는 키워드의 경우를 제외하고 데이터베이스의 데이터 정렬이 대/소문자를 구분하지 않는 경우에도 대/소문자와 액센트 기호를 구분합니다. 일치 작업은 키워드의 축약 형식을 구분하지 않습니다. 예를 들어 EXECUTE, EXEC 및 execute 키워드는 동일하게 간주됩니다.

계획 지침을 쿼리와 일치시키는 방법은 계획 지침을 사용하여 배포된 응용 프로그램의 쿼리 최적화를 참조하십시오.

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

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

사용 권한

**@type='OBJECT'**를 지정하여 OBJECT 유형의 계획 지침을 만들려면 참조되는 개체에 대한 ALTER 권한이 있어야 합니다. SQL 또는 TEMPLATE 유형의 계획 지침을 만들려면 현재 데이터베이스에 대한 ALTER 권한이 있어야 합니다.

예

1. 저장 프로시저에 있는 쿼리에 대해 OBJECT 유형의 계획 지침 만들기

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

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

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry 
    (@Country 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;
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',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))';

2. 독립 실행형 쿼리에 대해 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)';

3. 매개 변수가 있는 쿼리 형식에 대해 TEMPLATE 유형의 계획 지침 만들기

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

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

SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.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 AdventureWorks.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks.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 AdventureWorks.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks.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)';

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

TEMPLATE 기반의 계획 지침에서 사용하기 위해 매개 변수가 있는 쿼리 형식을 얻는 방법은 매개 변수가 있는 쿼리를 위한 계획 지침 디자인을 참조하십시오.

4. API 커서 요청을 사용하여 전송한 쿼리에 대해 계획 지침 만들기

계획 지침을 API 서버 커서 루틴에서 전송된 쿼리에 일치시킬 수 있습니다. 이러한 루틴에는 sp_cursorprepare, sp_cursorprepexec 및 sp_cursoropen이 있습니다. ADO, OLE DB 및 ODBC API를 사용하는 응용 프로그램은 API 서버 커서를 사용하여 SQL Server와 자주 상호 작용합니다. 자세한 내용은 API 서버 커서를 참조하십시오. RPC:Starting 프로파일러 추적 이벤트를 보고 SQL Server 프로파일러 추적에서 API 서버 커서 루틴의 호출을 확인할 수 있습니다.

계획 지침으로 튜닝하려는 쿼리에 대해 다음 데이터가 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 h INNER JOIN AdventureWorks.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)';

응용 프로그램에서 수행하는 이 쿼리의 후속 실행은 이 계획 지침의 영향을 받으며 쿼리를 처리하는 데 해시 조인이 사용됩니다.

커서를 사용하여 전송된 쿼리의 계획 지침에 USE PLAN 쿼리 힌트를 사용하는 방법은 커서 쿼리에서 USE PLAN 쿼리 힌트 사용을 참조하십시오.

참고 항목

참조

sp_control_plan_guide(Transact-SQL)
sys.plan_guides
데이터베이스 엔진 저장 프로시저(Transact-SQL)
시스템 저장 프로시저(Transact-SQL)

도움말 및 정보

SQL Server 2005 지원 받기

변경 내역

릴리스	내역
2005년 12월 5일	새로운 내용 SQL 계획 지침에 대해 PARAMETERIZATION { FORCED \| SIMPLE } 쿼리 힌트를 지정할 수 없다는 내용을 추가했습니다. 주의 섹션에 구문 순서와 일관성에 대한 지침에 대한 설명을 추가했습니다. 변경된 내용 OBJECT 계획 지침은 암호화된 개체나 임시 개체를 참조할 수 없음에 대해 자세한 설명을 추가했습니다.
2007년 9월 15일	업데이트된 내용 @type = 'SQL'이고 @module_or_batch가 NULL로 설정된 경우의 statement_text에 대한 계획 지침 일치 요구 사항의 설명을 추가했습니다. 계획 캐시에 계획 지침을 만드는 데 따르는 영향에 대한 정보를 추가했습니다.

2005년 12월 5일

새로운 내용

SQL 계획 지침에 대해 PARAMETERIZATION { FORCED | SIMPLE } 쿼리 힌트를 지정할 수 없다는 내용을 추가했습니다.
주의 섹션에 구문 순서와 일관성에 대한 지침에 대한 설명을 추가했습니다.

변경된 내용

OBJECT 계획 지침은 암호화된 개체나 임시 개체를 참조할 수 없음에 대해 자세한 설명을 추가했습니다.

2007년 9월 15일

업데이트된 내용

@type = 'SQL'이고 @module_or_batch가 NULL로 설정된 경우의 statement_text에 대한 계획 지침 일치 요구 사항의 설명을 추가했습니다.
계획 캐시에 계획 지침을 만드는 데 따르는 영향에 대한 정보를 추가했습니다.

다음을 통해 공유