계획 지침Plan Guides

SQL Server 2017SQL Server 2017에서 실제 쿼리의 텍스트를 직접 변경할 수 없거나 직접 변경하지 않으려는 경우 계획 지침에 따라 쿼리 성능을 최적화할 수 있습니다.Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query in SQL Server 2017SQL Server 2017. 계획 지침은 쿼리 힌트 또는 고정 쿼리 계획을 연결하여 쿼리 최적화에 영향을 줍니다.Plan guides influence the optimization of queries by attaching query hints or a fixed query plan to them. 계획 안내는 타사 공급업체에서 제공된 데이터베이스 응용 프로그램의 일부 쿼리 하위 집합이 올바른 성능을 내지 못하는 경우에 유용합니다.Plan guides can be useful when a small subset of queries in a database application provided by a third-party vendor are not performing as expected. 계획 지침에서 최적화하려는 Transact-SQL 문을 지정하고 사용할 쿼리 힌트가 들어 있는 OPTION 절이나 쿼리를 최적화하는 데 사용할 특정 쿼리 계획을 지정합니다.In the plan guide, you specify the Transact-SQL statement that you want optimized and either an OPTION clause that contains the query hints you want to use or a specific query plan you want to use to optimize the query. 쿼리가 실행하면 SQL ServerSQL Server 가 Transact-SQL 문을 계획 지침과 대응시키고 런타임에 쿼리에 OPTION 절을 추가하거나 지정된 쿼리 계획을 사용합니다.When the query executes, SQL ServerSQL Server matches the Transact-SQL statement to the plan guide and attaches the OPTION clause to the query at run time or uses the specified query plan.

만들 수 있는 총 계획 지침 수는 사용 가능한 시스템 리소스에 의해서만 제한됩니다.The total number of plan guides you can create is limited only by available system resources. 그러나 계획 지침은 성능 향상이나 안정화를 목적으로 하는 중요 업무용 쿼리로 제한되어야 합니다.Nevertheless, plan guides should be limited to mission-critical queries that are targeted for improved or stabilized performance. 배포된 응용 프로그램의 쿼리 로드 대부분에 영향을 주기 위해 계획 지침을 사용하면 안 됩니다.Plan guides should not be used to influence most of the query load of a deployed application.

참고

계획 지침은 MicrosoftMicrosoft SQL ServerSQL Server의 일부 버전에서 사용할 수 없습니다.Plan guides cannot be used in every edition of MicrosoftMicrosoft SQL ServerSQL Server. SQL ServerSQL Server버전에서 지원되는 기능 목록은 [SQL Server 2016 버전에서 지원하는 기능](~/sql-server/editions-and-supported-features-for-sql-server-2016.md)을 참조하세요.For a list of features that are supported by the editions of SQL ServerSQL Server, see Features Supported by the Editions of SQL Server 2016. 계획 지침은 모든 버전에 표시됩니다.Plan guides are visible in any edition. 계획 지침이 포함된 데이터베이스를 모든 버전에 추가할 수 있습니다.You can also attach a database that contains plan guides to any edition. 업그레이드된 버전의 SQL ServerSQL Server에 데이터베이스를 복원하거나 첨부해도 계획 지침은 그대로 유지됩니다.Plan guides remain intact when you restore or attach a database to an upgraded version of SQL ServerSQL Server.

계획 지침의 유형Types of Plan Guides

다음과 같은 계획 지침 유형을 만들 수 있습니다.The following types of plan guides can be created.

OBJECT 계획 지침OBJECT plan guide
OBJECT 계획 지침은 Transact-SQLTransact-SQL 저장 프로시저, 사용자 정의 스칼라 함수, 다중 문 사용자 정의 테이블 반환 함수 및 DML 트리거의 컨텍스트에서 실행되는 쿼리와 일치합니다.An OBJECT plan guide matches queries that execute in the context of Transact-SQLTransact-SQL stored procedures, scalar user-defined functions, multi-statement table-valued user-defined functions, and DML triggers.

@Country데이터베이스에 대해 배포되는 데이터베이스 응용 프로그램에region AdventureWorks2012AdventureWorks2012 매개 변수를 가져오는 다음 저장 프로시저가 있다고 가정합니다.Suppose the following stored procedure, which takes the @Countryregion parameter, is in a database application that is deployed against the AdventureWorks2012AdventureWorks2012 database:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country_region nvarchar(60))  
AS  
BEGIN  
    SELECT *  
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c,   
        Sales.SalesTerritory AS t  
    WHERE h.CustomerID = c.CustomerID  
        AND c.TerritoryID = t.TerritoryID  
        AND CountryRegionCode = @Country_region  
END;  

이 저장 프로시저가 @Countryregion = N'AU' (오스트레일리아)에 맞게 컴파일되고 최적화되었다고 가정합니다.Assume that this stored procedure has been compiled and optimized for @Countryregion = N'AU' (Australia). 그러나 오스트레일리아에서 발주되는 판매 주문이 비교적 적기 때문에 판매 주문이 더 많은 국가의 매개 변수 값을 사용하여 쿼리를 실행할 경우 성능이 저하됩니다.However, because there are relatively few sales orders that originate from Australia, performance decreases when the query executes using parameter values of countries with more sales orders. 미국이 판매 주문을 가장 많이 내므로 @Country_region = N'US' 매개 변수의 가능한 모든 값에 대해 @Country_region 에 대해 생성된 쿼리 계획이 더 잘 수행될 가능성이 높습니다.Because the most sales orders originate in the United States, a query plan that is generated for @Country_region = N'US' would likely perform better for all possible values of the @Country_region parameter.

저장 프로시저를 수정하여 OPTIMIZE FOR 쿼리 힌트를 쿼리에 추가하면 이 문제를 해결할 수 있습니다.You could address this problem by modifying the stored procedure to add the OPTIMIZE FOR query hint to the query. 그러나 저장 프로시저가 배포된 응용 프로그램 안에 있기 때문에 응용 프로그램 코드를 직접 수정할 수 없습니다.However, because the stored procedure is in a deployed application, you cannot directly modify the application code. 대신 AdventureWorks2012AdventureWorks2012 데이터베이스에서 다음 계획 지침을 만들 수 있습니다.Instead, you can create the following plan guide in the AdventureWorks2012AdventureWorks2012 database.

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

sp_create_plan_guide 문에 지정되어 있는 쿼리가 실행되면 OPTIMIZE FOR (@Country = N''US'') 절을 포함하도록 최적화 이전에 쿼리가 수정됩니다.When the query specified in the sp_create_plan_guide statement executes, the query is modified before optimization to include the OPTIMIZE FOR (@Country = N''US'') clause.

SQL 계획 지침SQL plan guide
SQL 계획 지침은 데이터베이스 개체의 일부가 아닌 독립 실행형 Transact-SQLTransact-SQL 문과 일괄 처리의 컨텍스트에서 실행되는 쿼리와 일치합니다.An SQL plan guide matches queries that execute in the context of stand-alone Transact-SQLTransact-SQL statements and batches that are not part of a database object. SQL 기반 계획 지침은 지정된 형식으로 매개 변수화되는 쿼리와 일치되도록 하는 데도 사용될 수 있습니다.SQL-based plan guides can also be used to match queries that parameterize to a specified form. SQL 계획 지침은 독립 실행형 Transact-SQLTransact-SQL 문 및 일괄 처리에 적용됩니다.SQL plan guides apply to stand-alone Transact-SQLTransact-SQL statements and batches. 이러한 문은 종종 응용 프로그램에서 sp_executesql 시스템 저장 프로시저를 사용하여 제출됩니다.Frequently, these statements are submitted by an application by using the sp_executesql system stored procedure. 예를 들어 다음 독립 실행형 일괄 처리를 생각해 보십시오.For example, consider the following stand-alone batch:

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

병렬 실행 계획이 이 쿼리에서 생성되지 않도록 하려면 다음 계획 지침을 만들고 MAXDOP 쿼리 힌트를 1 매개 변수의 @hints 로 설정합니다.To prevent a parallel execution plan from being generated on this query, create the following plan guide and set the MAXDOP query hint to 1 in the @hints parameter.

sp_create_plan_guide   
@name = N'Guide2',   
@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)';  
중요

@module_or_batch 문의 @paramssp_create_plan guide 인수에 대해 제공되는 값은 실제 쿼리에서 전송되는 해당 텍스트와 일치해야 합니다.The values that are supplied for the @module_or_batch and @params arguments of the sp_create_plan guide statement must match the corresponding text submitted in the actual query. 자세한 내용은 sp_create_plan_guide(Transact-SQL) 문의 SQL Server Profiler를 사용하여 계획 지침 작성 및 테스트에서 실제 쿼리의 텍스트를 직접 변경할 수 없거나 직접 변경하지 않으려는 경우 계획 지침에 따라 쿼리 성능을 최적화할 수 있습니다.For more information, see sp_create_plan_guide (Transact-SQL) and Use SQL Server Profiler to Create and Test Plan Guides.

PARAMETERIZATION 데이터베이스 옵션을 FORCED로 설정했거나 쿼리 클래스를 매개 변수화하도록 지정하는 TEMPLATE 계획 지침을 만든 경우에 같은 형식으로 매개 변수화된 쿼리에 대해서도 SQL 계획 지침을 만들 수 있습니다.SQL plan guides can also be created on queries that parameterize to the same form when the PARAMETERIZATION database option is SET to FORCED, or when a TEMPLATE plan guide is created specifying that a parameterized class of queries.

TEMPLATE 계획 지침TEMPLATE plan guide
TEMPLATE 계획 지침은 지정된 형식으로 매개 변수화되는 독립 실행형 쿼리와 일치합니다.A TEMPLATE plan guide matches stand-alone queries that parameterize to a specified form. 이 계획 지침은 쿼리 클래스에 대한 데이터베이스의 현재 PARAMETERIZATION 데이터베이스 SET 옵션을 대체하는 데 사용됩니다.These plan guides are used to override the current PARAMETERIZATION database SET option of a database for a class of queries.

다음과 같은 경우 TEMPLATE 계획 지침을 만들 수 있습니다.You can create a TEMPLATE plan guide in either of the following situations:

  • PARAMETERIZATION 데이터베이스 옵션이 FORCED로 설정되었지만 단순 매개 변수화 규칙에 따라 컴파일하려는 쿼리가 있는 경우The PARAMETERIZATION database option is SET to FORCED, but there are queries you want compiled according to the rules of simple parameterization.

  • PARAMETERIZATION 데이터베이스 옵션이 SIMPLE(기본 설정)로 설정되었지만 쿼리 클래스에 대해 강제 매개 변수화를 시도하려는 경우The PARAMETERIZATION database option is SET to SIMPLE (the default setting), but you want forced parameterization to be tried on a class of queries.

계획 지침 일치 요구 사항Plan Guide Matching Requirements

계획 지침의 범위는 이 지침이 생성되는 데이터베이스입니다.Plan guides are scoped to the database in which they are created. 따라서 쿼리를 실행할 때 현재 데이터베이스에 있는 계획 지침만 쿼리에 일치시킬 수 있습니다.Therefore, only plan guides that are in the database that is current when a query executes can be matched to the query. 예를 들어 AdventureWorks2012AdventureWorks2012 가 현재 데이터베이스이면 다음 쿼리가 실행됩니다.For example, if AdventureWorks2012AdventureWorks2012 is the current database and the following query executes:

SELECT FirstName, LastName FROM Person.Person;

AdventureWorks2012AdventureWorks2012 데이터베이스의 계획 지침만 이 쿼리에 일치될 수 있습니다.Only plan guides in the AdventureWorks2012AdventureWorks2012 database are eligible to be matched to this query. 그러나 AdventureWorks2012AdventureWorks2012 가 현재 데이터베이스이면 다음 문이 실행됩니다.However, if AdventureWorks2012AdventureWorks2012 is the current database and the following statements are run:

USE DB1;

SELECT FirstName, LastName FROM Person.Person;

쿼리가 DB1 컨텍스트에서 실행되므로 DB1의 계획 지침만 쿼리에 일치됩니다.Only plan guides in DB1 are eligible to be matched to the query because the query is executing in the context of DB1.

SQL 또는 TEMPLATE 기반 계획 지침을 위해 SQL ServerSQL Server는 두 값의 문자를 비교하여 @module_or_batch 및 @params 인수의 값을 일치시킵니다.For SQL- or TEMPLATE-based plan guides, SQL ServerSQL Server matches the values for the @module_or_batch and @params arguments to a query by comparing the two values character by character. 따라서 SQL ServerSQL Server 가 실제 일괄 처리에서 수신한 것과 똑같이 텍스트를 제공해야 합니다.This means you must provide the text exactly as SQL ServerSQL Server receives it in the actual batch.

@type이 'SQL'이고 @module_or_batch가 NULL로 설정된 경우 @module_or_batch 값은 @stmt 값으로 설정됩니다.When @type = 'SQL' and @module_or_batch is set to NULL, the value of @module_or_batch is set to the value of @stmt.</span></span> 이는 statement_text 의 값이 SQL ServerSQL Server에 전송된 것과 문자 수준까지 같은 형식으로 제공되어야 함을 의미합니다.This means that the value for statement_text must be provided in the identical format, character-for-character, as it is submitted to SQL ServerSQL Server. 이 일치 작업을 더 효과적으로 처리하기 위해 내부 변환은 수행되지 않습니다.No internal conversion is performed to facilitate this match.

일반(SQL 또는 OBJECT) 계획 지침 및 TEMPLATE 계획 지침 모두 문에 적용할 수 있을 경우 일반 계획 지침만 사용됩니다.When both a regular (SQL or OBJECT) plan guide and a TEMPLATE plan guide can apply to a statement, only the regular plan guide will be used.

참고

계획 지침을 만들려는 문이 포함된 일괄 처리는 USE database 문을 포함할 수 없습니다.The batch that contains the statement on which you want to create a plan guide cannot contain a USE database statement.

계획 캐시의 계획 지침 효과Plan Guide Effect on the Plan Cache

모듈에 대한 계획 지침을 만들면 계획 캐시에서 해당 모듈에 대한 쿼리 계획이 제거되고,Creating a plan guide on a module removes the query plan for that module from the plan cache. 일괄 처리에 OBJECT 또는 SQL 유형의 계획 지침을 만들면 같은 해시 값을 가진 일괄 처리에 대한 쿼리 계획이 제거되며,Creating a plan guide of type OBJECT or SQL on a batch removes the query plan for a batch that has the same hash value. TEMPLATE 유형의 계획 지침을 만들면 해당 데이터베이스 내의 계획 캐시에서 단일 문 일괄 처리가 모두 제거됩니다.Creating a plan guide of type TEMPLATE removes all single-statement batches from the plan cache within that database.

태스크Task 항목Topic
계획 지침을 만드는 방법에 대해 설명합니다.Describes how to create a plan guide. 새 계획 지침 만들기Create a New Plan Guide
매개 변수가 있는 쿼리에 대한 계획 지침을 만드는 방법에 대해 설명합니다.Describes how to create a plan guide for parameterized queries. 매개 변수가 있는 쿼리를 위한 계획 지침 만들기Create a Plan Guide for Parameterized Queries
계획 지침을 사용하여 쿼리 매개 변수화 동작을 제어하는 방법에 대해 설명합니다.Describes how to control query parameterization behavior by using plan guides. 계획 지침을 사용하여 쿼리 매개 변수화 동작 지정Specify Query Parameterization Behavior by Using Plan Guides
계획 지침에 고정 쿼리를 포함하는 방법에 대해 설명합니다.Describes how to include a fixed query plan in a plan guide. 계획 지침에 정해진 쿼리 계획 적용Apply a Fixed Query Plan to a Plan Guide
계획 지침에서 쿼리 힌트를 지정하는 방법에 대해 설명합니다.Describes how to specify query hints in a plan guide. 계획 지침에 쿼리 힌트 연결Attach Query Hints to a Plan Guide
계획 지침 속성을 보는 방법에 대해 설명합니다.Describes how to view plan guide properties. 계획 지침 속성 보기View Plan Guide Properties
SQL Server Profiler를 사용하여 계획 지침을 작성 및 테스트하는 방법에 대해 설명합니다.Describes how to use SQL Server Profiler to create and test plan guides. SQL Server Profiler를 사용하여 계획 지침 작성 및 테스트Use SQL Server Profiler to Create and Test Plan Guides
계획 지침의 유효성을 검사하는 방법에 대해 설명합니다.Describes how to validate plan guides. 업그레이드 후 계획 지침의 유효성 검사Validate Plan Guides After Upgrade

참고 항목See Also

sp_create_plan_guide(Transact-SQL) sp_create_plan_guide (Transact-SQL)
sp_create_plan_guide_from_handle(Transact-SQL) sp_create_plan_guide_from_handle (Transact-SQL)
sp_control_plan_guide(Transact-SQL) sp_control_plan_guide (Transact-SQL)
sys.plan_guides(Transact-SQL) sys.plan_guides (Transact-SQL)
sys.fn_validate_plan_guide(Transact-SQL) sys.fn_validate_plan_guide (Transact-SQL)