계획 지침 이해

이 항목에서는 계획 지침에 대해 설명하며, 텍스트를 직접 변경할 수 없거나 직접 변경하지 않으려는 경우 계획 지침을 사용하여 쿼리 성능을 최적화하는 방법에 대해서도 설명합니다. 계획 지침은 타사 공급업체에서 배포한 데이터베이스 응용 프로그램의 일부 쿼리 하위 집합이 올바른 성능을 내지 못하는 경우에 유용합니다. 계획 지침은 쿼리 힌트나 정해진 쿼리 계획을 쿼리에 연결하여 쿼리 최적화에 영향을 미칩니다. 계획 지침에서 최적화하려는 Transact-SQL 문을 지정하고 사용할 쿼리 힌트가 들어 있는 OPTION 절이나 쿼리를 최적화하는 데 사용할 특정 쿼리 계획을 지정합니다. 쿼리가 실행하면 SQL Server가 Transact-SQL 문을 계획 지침과 대응시키고 런타임에 쿼리에 OPTION 절을 추가하거나 지정된 쿼리 계획을 사용합니다.

[!참고]

계획 지침은 SQL Server Standard, Developer, Evaluation 및 Enterprise 버전에서만 사용할 수 있지만 보기는 모든 버전에서 가능합니다. 계획 지침이 포함된 데이터베이스를 모든 버전에 추가할 수 있습니다. 업그레이드된 버전의 SQL Server 2008에 데이터베이스를 복원하거나 첨부해도 계획 지침은 그대로 유지됩니다.

계획 지침 대 쿼리 일치

다음 컨텍스트에서 실행되는 쿼리와 일치하는 계획 지침을 만들 수 있습니다.

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

• SQL 계획 지침은 데이터베이스 개체의 일부가 아닌 독립 실행형 Transact-SQL 문과 일괄 처리의 컨텍스트에서 실행되는 쿼리와 일치합니다. SQL 기반 계획 지침은 지정된 형식으로 매개 변수화되는 쿼리와 일치되도록 하는 데도 사용될 수 있습니다.

• TEMPLATE 계획 지침은 지정된 형식으로 매개 변수화되는 독립 실행형 쿼리와 일치합니다. 이들 계획 지침은 쿼리 클래스에 대한 데이터베이스의 현재 PARAMETERIZATION 데이터베이스 SET 옵션을 재정의하는 데 사용됩니다. 자세한 내용은 단순 매개 변수화 및 강제 매개 변수화를 참조하십시오.

자세한 내용은 SQL Server에서 계획 지침을 쿼리에 대응시키는 방법을 참조하십시오.

OBJECT 계획 지침

@Country\_region 매개 변수를 사용하는 다음과 같은 저장 프로시저가 AdventureWorks 데이터베이스에 대해 배포되는 데이터베이스 응용 프로그램에 존재한다고 가정할 수 있습니다.

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 = N'AU'(오스트레일리아)에 맞게 컴파일되고 최적화되었다고 가정합니다. 그러나 오스트레일리아에서 발주되는 판매 주문이 비교적 적기 때문에 판매 주문이 더 많은 국가의 매개 변수 값을 사용하여 쿼리를 실행할 경우 성능이 저하됩니다. 미국이 판매 주문을 가장 많이 내므로 @Country\_region 매개 변수의 가능한 모든 값에 대해 @Country\_region = N'US'에 대해 생성된 쿼리 계획이 더 잘 수행될 가능성이 높습니다.

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

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 문 및 일괄 처리에 적용됩니다. 이러한 문은 종종 응용 프로그램에서 sp_executesql 시스템 저장 프로시저를 사용하여 제출됩니다. 예를 들어 다음 독립 실행형 일괄 처리를 생각해 보십시오.

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

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

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

[!참고]

계획 지침을 만들려는 문이 포함된 일괄 처리는 USE database 문을 포함할 수 없습니다.

중요
sp_create_plan guide 문의 @module_or_batch 및 @params 인수에 대해 제공되는 값은 실제 쿼리에서 전송되는 해당 텍스트와 일치해야 합니다. 자세한 내용은 sp_create_plan_guide(Transact-SQL) 및 SQL Server 프로파일러를 사용하여 계획 지침 작성 및 테스트를 참조하십시오.

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

TEMPLATE 계획 지침

TEMPLATE 계획 지침은 특정 쿼리 형식에 대한 매개 변수화 동작을 재정의하는 데 사용됩니다. 다음과 같은 경우 TEMPLATE 계획 지침을 만들 수 있습니다.

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

• PARAMETERIZATION 데이터베이스 옵션이 SIMPLE(기본 설정)로 설정되었지만 쿼리 클래스에 대해 강제 매개 변수화를 시도하려는 경우

자세한 내용은 계획 지침을 사용하여 쿼리 매개 변수화 동작 지정을 참조하십시오.

다음 예에서는 지정된 형식으로 매개 변수화되는 쿼리와 일치하는 계획 지침을 만들고 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 계획 지침은 SQL 계획 지침과도 함께 사용할 수 있습니다. 예를 들어 쿼리 클래스가 매개 변수화되도록 TEMPLATE 계획 지침을 만들 수 있습니다. 그런 다음 매개 변수가 있는 해당 쿼리 형식에 대한 SQL 계획 지침을 만들 수 있습니다.

계획 지침에 정해진 쿼리 계획 적용

OBJECT 또는 SQL 유형의 계획 지침에 정해진 쿼리 계획을 적용할 수 있습니다. 정해진 쿼리 계획을 적용하는 계획 지침은 최적화 프로그램에서 특정 쿼리에 대해 선택한 실행 계획보다 더 뛰어난 기존 실행 계획을 알고 있는 경우 유용합니다.

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

USE AdventureWorks;
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

업그레이드 후 계획 지침의 유효성 검사

새로운 릴리스의 SQL Server로 응용 프로그램을 업그레이드할 때는 계획 지침 정의를 다시 평가하고 테스트하는 것이 좋습니다. 성능 조정 요구 사항과 계획 지침 일치 동작은 변경될 수 있습니다. 잘못된 계획 지침으로 인해 쿼리가 실패하지는 않지만 이 경우 계획 지침을 사용하지 않은 채 계획이 컴파일되므로 최상의 선택이 아닐 수 있습니다. 데이터베이스를 SQL Server 2008로 업그레이드한 후에는 다음 태스크를 수행하는 것이 좋습니다.

• sys.fn_validate_plan_guide 함수를 사용하여 기존 계획 지침의 유효성을 검사합니다.

• SQL Server Profiler의 Plan Guide Unsuccessful 이벤트를 사용하여 잘못된 계획 지침이 있는지 모니터링합니다.

참고 항목

참조

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)

쿼리 힌트(Transact-SQL)

개념

계획 지침 디자인 및 구현

강제 매개 변수화

단순 매개 변수화

관련 자료

계획 지침을 사용하여 배포된 응용 프로그램의 쿼리 최적화