계획 지침 이해

  • 아티클

SQL Server 2005에서는 쿼리 성능을 최적화하는 계획 지침을 만들기 위한 sp_create_plan_guide 시스템 저장 프로시저를 제공합니다. 이 프로시저는 쿼리 텍스트를 직접 변경할 수 없거나 변경하지 않으려는 경우에 사용할 수 있습니다. 계획 지침은 타사 공급업체에서 배포한 데이터베이스 응용 프로그램의 일부 쿼리 하위 집합이 올바른 성능을 내지 못하는 경우에 유용합니다. 계획 지침은 쿼리 힌트를 쿼리에 연결하여 쿼리의 최적화에 영향을 미칩니다. sp_create_plan_guide 문에서 최적화하려는 쿼리와 쿼리를 최적화하는 데 사용할 쿼리 힌트가 있는 OPTION 절을 지정합니다. 쿼리가 실행되면 SQL Server는 쿼리를 계획 지침에 대조하여 런타임에 쿼리에 OPTION 절을 연결합니다.

[!참고] 계획 지침은 SQL Server 2005 Standard, Developer, Evaluation 및 Enterprise Edition에서만 만들고 사용할 수 있습니다. 계획 지침 삭제는 모든 버전에서 가능합니다.

계획 지침을 사용할 경우 도움이 되는 쿼리는 일반적으로 매개 변수 기반이며 매개 변수 값이 최악의 시나리오나 전형적인 시나리오를 나타내지 않는 캐시된 쿼리 계획을 사용하므로 성능이 좋지 않을 수 있습니다. OPTIMIZE FOR 및 RECOMPILE 쿼리 힌트를 사용하면 이러한 문제를 해결할 수 있습니다. OPTIMIZE FOR는 쿼리가 최적화될 때 매개 변수에 특정 값을 사용하도록 SQL Server에 지시합니다. RECOMPILE은 실행 후 쿼리 계획을 삭제하도록 서버에 지시하여 다음 번에 같은 쿼리를 실행할 때 쿼리 최적화 프로그램이 새로운 쿼리 계획을 다시 컴파일하도록 합니다.

계획 지침과 함께 사용할 다른 일반 힌트로는 USE PLAN 쿼리 힌트가 있습니다. 이 쿼리 힌트는 최적화 프로그램에서 특정 쿼리에 대해 선택한 실행 계획이 더 잘 수행되기 때문에 이 계획으로 기존 실행 계획을 대체할 수 있음을 미리 알고 있는 경우에 적용됩니다. USE PLAN은 SQL Server가 쿼리 실행 시 힌트 구문에 명시적으로 지정된 특정 쿼리 계획을 사용하게 합니다. USE PLAN 쿼리 힌트를 적용하는 계획 지침은 조인 순서를 강제 설정하거나 조인 힌트를 사용하거나 인덱스 힌트를 사용하도록 쿼리를 다시 작성하여 쿼리에 적합한 실행 계획을 가져오는 것이 가장 편리할 경우에 특히 유용합니다. 자세한 내용은 계획 강제 적용 시나리오: 다시 작성한 쿼리에서 얻은 계획을 적용할 계획 지침 만들기를 참조하십시오.

RECOMPILE, OPTIMIZE FOR, USE PLAN 및 기타 쿼리 힌트에 대한 자세한 내용은 쿼리 힌트(Transact-SQL)를 참조하십시오.

ms190417.Caution(ko-kr,SQL.90).gif주의:
쿼리 힌트를 오용하는 계획 지침을 사용하면 컴파일, 실행 또는 성능 문제가 발생할 수 있습니다. 계획 지침은 숙련된 개발자와 데이터베이스 관리자만 사용해야 합니다.

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

  • OBJECT 계획 지침은 Transact-SQL 저장 프로시저, 스칼라 함수, 다중 문 테이블 값 함수 및 DML 트리거의 컨텍스트에서 실행되는 쿼리와 일치합니다.
  • SQL 계획 지침은 데이터베이스 개체의 일부가 아닌 독립 실행형 Transact-SQL 문과 일괄 처리의 컨텍스트에서 실행되는 쿼리와 일치합니다. SQL 기반 계획 지침은 지정된 형식으로 매개 변수화되는 쿼리와 일치되도록 하는 데도 사용될 수 있습니다.
  • TEMPLATE 계획 지침은 지정된 형식으로 매개 변수화되는 독립 실행형 쿼리와 일치합니다. 이들 계획 지침은 쿼리 클래스에 대한 데이터베이스의 현재 PARAMETERIZATION 데이터베이스 SET 옵션을 재정의하는 데 사용됩니다.

OBJECT 계획 지침

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

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

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

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

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

sp_create_plan_guide 문에 지정되어 있는 쿼리가 실행되면 함께 지정되어 있는 OPTIMIZE FOR (@Country = N''US'') 절도 포함하도록 최적화 이전에 쿼리가 수정됩니다.

SQL 계획 지침

SQL 계획 지침은 응용 프로그램에서 sp_executesql 시스템 저장 프로시저를 사용하여 자주 제출하는 문과 일괄 처리에 적용됩니다. 예를 들어 다음 독립 실행형 일괄 처리를 생각해 보십시오.

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

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

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

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

ms190417.note(ko-kr,SQL.90).gif중요:
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(기본 설정)로 설정되었지만 쿼리 클래스에 대해 강제 매개 변수화를 시도하려는 경우

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

또한 TEMPLATE 계획 지침은 SQL 계획 지침과도 함께 사용할 수 있습니다. 예를 들어 쿼리 클래스가 매개 변수화되도록 TEMPLATE 계획 지침을 만들 수 있습니다. 그런 다음 매개 변수가 있는 해당 쿼리 형식에 대한 SQL 계획 지침을 만들 수 있습니다.

참고 항목

개념

계획 지침을 사용하여 배포된 응용 프로그램의 쿼리 최적화
계획 지침 디자인 및 구현

관련 자료

쿼리 성능
sp_create_plan_guide(Transact-SQL)
sp_control_plan_guide(Transact-SQL)
sys.plan_guides

도움말 및 정보

SQL Server 2005 지원 받기