계획 지침

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

계획 가이드를 사용하면 SQL Server에서 실제 쿼리의 텍스트를 직접 변경할 수 없거나 변경하지 않으려는 경우 쿼리 성능을 최적화할 수 있습니다. 계획 지침은 쿼리 힌트 또는 고정 쿼리 계획을 쿼리에 연결하여 쿼리 최적화에 영향을 줍니다. 계획 지침은 타사 공급업체에서 제공하는 데이터베이스 애플리케이션의 작은 쿼리 하위 집합이 예상대로 수행되지 않는 경우에 유용할 수 있습니다. 계획 가이드에서는 최적화하려는 Transact-SQL 문과 사용하려는 쿼리 힌트가 포함된 OPTION 절 또는 쿼리를 최적화하는 데 사용할 특정 쿼리 계획을 지정합니다. 쿼리가 실행되면 SQL Server는 Transact-SQL 문을 계획 지침에 일치시키고 런타임에 OPTION 절을 쿼리에 연결하거나 지정된 쿼리 계획을 사용합니다. SQL Server 쿼리 최적화 프로그램은 일반적으로 쿼리에 가장 적합한 실행 계획을 선택하므로 숙련된 개발자 및 데이터베이스 관리자를 위한 최후의 수단으로 계획 가이드만 사용하는 것이 좋습니다.

만들 수 있는 계획 가이드의 총 수는 사용 가능한 시스템 리소스에 의해서만 제한됩니다. 그럼에도 불구하고 계획 지침은 성능 향상 또는 안정화를 목표로 하는 중요 업무용 쿼리로 제한되어야 합니다. 배포된 애플리케이션의 쿼리 로드 대부분에 영향을 주기 위해 계획 지침을 사용하면 안 됩니다.

이 기능에 의해 강제 적용되는 결과 실행 계획은 강제 적용되는 계획과 동일하거나 유사합니다. 결과 계획이 계획 지침에 지정된 계획과 동일하지 않을 수 있으므로 계획의 성능이 다를 수 있습니다. 드문 경우지만 성능 차이가 상당하고 부정적일 수 있습니다. 이 경우 관리자는 강제 적용된 계획을 제거해야 합니다.

계획 지침은 Microsoft SQL Server의 모든 버전에서 사용할 수 없습니다. SQL Server버전에서 지원되는 기능 목록은 SQL Server 2016 버전에서 지원하는 기능을 참조하세요. 플랜 가이드는 모든 버전에서 볼 수 있습니다. 계획 지침이 포함된 데이터베이스를 모든 버전에 추가할 수 있습니다. 업그레이드된 버전의 SQL Server에 데이터베이스를 복원하거나 연결할 때 계획 지침은 그대로 유지됩니다.

계획 지침의 유형

다음과 같은 계획 지침 유형을 만들 수 있습니다.

OBJECT 계획 지침

OBJECT 계획 지침은 Transact-SQL 저장 프로시저, 사용자 정의 스칼라 함수, 다중 문 사용자 정의 테이블 반환 함수 및 DML 트리거의 컨텍스트에서 실행되는 쿼리와 일치합니다.

매개 변수를 사용하는 @Country_region 다음 저장 프로시저가 데이터베이스에 대해 배포된 데이터베이스 애플리케이션에 AdventureWorks2022 있다고 가정합니다.

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;  

이 저장 프로시저가 (오스트레일리아)에 대해 @Country_region = N'AU' 컴파일되고 최적화되었다고 가정합니다. 그러나 오스트레일리아에서 발생하는 판매 주문이 상대적으로 적기 때문에 판매 주문이 많은 국가/지역의 매개 변수 값을 사용하여 쿼리를 실행하면 성능이 저하됩니다. 미국이 판매 주문을 가장 많이 내므로 @Country_region = N'US' 매개 변수의 가능한 모든 값에 대해 @Country_region 에 대해 생성된 쿼리 계획이 더 잘 수행될 가능성이 높습니다.

저장 프로시저를 수정하여 OPTIMIZE FOR 쿼리 힌트를 쿼리에 추가하면 이 문제를 해결할 수 있습니다. 그러나 저장 프로시저가 배포된 애플리케이션 안에 있기 때문에 애플리케이션 코드를 직접 수정할 수 없습니다. 대신 데이터베이스에서 다음 계획 지침을 AdventureWorks2022 만들 수 있습니다.

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'') 수정됩니다.

SQL 계획 가이드

SQL 계획 지침은 데이터베이스 개체의 일부가 아닌 독립 실행형 Transact-SQL 문과 일괄 처리의 컨텍스트에서 실행되는 쿼리와 일치합니다. SQL 기반 계획 가이드를 사용하여 매개 변수화되는 쿼리를 지정된 형식으로 일치시킬 수도 있습니다. SQL 계획 지침은 독립 실행형 Transact-SQL 문 및 일괄 처리에 적용됩니다. 이러한 문은 sp_executesql 시스템 저장 프로시저를 사용하여 애플리케이션에서 제출하는 경우가 많습니다. 예를 들어 다음 독립 실행형 일괄 처리를 고려합니다.

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

이 쿼리에서 병렬 실행 계획이 생성되지 않도록 하려면 다음 계획 지침을 만들고 매개 변수에 @hints 쿼리 힌트를 1 설정합니다MAXDOP.

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)';  

또 다른 예에서는 sp_executesql을 사용하여 제출된 다음 SQL 문을 고려합니다.

exec sp_executesql N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id', N'@so_id int', @so_id = 43662;  

이 쿼리를 실행할 때마다 고유한 계획을 만들려면 다음 계획 지침을 만들고 @hints 매개 변수의 OPTION (RECOMPILE) 쿼리 힌트를 사용합니다.

exec sp_create_plan_guide   
@name = N'PlanGuide1_SalesOrders',   
@stmt = N'SELECT * FROM Sales.SalesOrderHeader
where SalesOrderID =  @so_id',
@type = N'SQL',  
@module_or_batch = NULL,   
@params = N'@so_id int',   
@hints = N'OPTION (recompile)';

PARAMETERIZATION 데이터베이스 옵션을 FORCED로 설정했거나 쿼리 클래스를 매개 변수화하도록 지정하는 TEMPLATE 계획 지침을 만든 경우에 같은 형식으로 매개 변수화된 쿼리에 대해서도 SQL 계획 지침을 만들 수 있습니다.

TEMPLATE 계획 가이드

TEMPLATE 계획 가이드는 지정된 양식으로 매개 변수화되는 독립 실행형 쿼리와 일치합니다. 이 계획 지침은 쿼리 클래스에 대한 데이터베이스의 현재 PARAMETERIZATION 데이터베이스 SET 옵션을 대체하는 데 사용됩니다.

다음 상황 중 하나에서 템플릿 계획 가이드를 만들 수 있습니다.

• PARAMETERIZATION 데이터베이스 옵션이 FORCED로 설정되었지만 단순 매개 변수화 규칙에 따라 컴파일하려는 쿼리가 있는 경우

• PARAMETERIZATION 데이터베이스 옵션은 SIMPLE(기본 설정)로 설정되지만 강제 매개 변수화 는 쿼리 클래스에서 시도하려고 합니다.

계획 가이드 일치 요구 사항

계획 지침은 만들어진 데이터베이스로 범위가 지정됩니다. 따라서 쿼리가 실행될 때 현재 데이터베이스에 있는 계획 지침만 쿼리와 일치시킬 수 있습니다. 예를 들어 AdventureWorks2022 가 현재 데이터베이스이면 다음 쿼리가 실행됩니다.

SELECT FirstName, LastName FROM Person.Person;

데이터베이스의 AdventureWorks2022 계획 지침만 이 쿼리와 일치시킬 수 있습니다. 그러나 현재 데이터베이스인 경우 AdventureWorks2022 다음 문이 실행됩니다.

USE DB1; 
SELECT FirstName, LastName FROM Person.Person;

쿼리가 DB1 컨텍스트에서 실행되므로 DB1의 계획 지침만 쿼리에 일치됩니다.

SQL 또는 TEMPLATE 기반 계획 가이드의 경우 SQL Server는 두 값 문자를 문자별로 비교하여 쿼리에 대한 @module_or_batch 값과 @params 인수의 값을 일치합니다. 즉, SQL Server가 실제 일괄 처리에서 수신하는 대로 텍스트를 정확하게 제공해야 합니다.

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

일반(SQL 또는 OBJECT) 계획 가이드와 TEMPLATE 계획 가이드를 모두 문에 적용할 수 있는 경우 일반 계획 가이드만 사용됩니다.

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

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

관련 작업

참고 항목

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