아티클
04/01/2012

계획 적용 시나리오: 다시 작성한 쿼리에서 얻은 계획을 적용할 계획 지침 만들기

대개 개선된 쿼리 계획을 얻을 수 있는 가장 편리한 방법은 쿼리 힌트를 사용하여 쿼리의 논리적 의미는 변경하지 않으면서 조인 순서, 조인 알고리즘 또는 인덱스 사용을 강제 적용하도록 쿼리를 수동으로 다시 작성하는 것입니다. 그러나 쿼리가 배포된 응용 프로그램 내에 있다면 이 방법을 사용하지 못할 수도 있습니다. 계획 지침을 사용하면 이러한 상황에 도움이 됩니다. 계획 지침은 쿼리 텍스트를 직접 변경할 수 없거나 직접 변경하는 것이 바람직하지 않은 경우 쿼리 계획에 쿼리 힌트를 연결하여 작동합니다. 자세한 내용은 계획 지침을 사용하여 배포된 응용 프로그램의 쿼리 최적화를 참조하십시오.

쿼리를 직접 다시 작성하려면 쿼리에 대한 계획을 캡처한 다음 캡처한 계획이 포함되어 있는 계획 지침과 함께 캡처한 계획을 원래 쿼리에 적용하고 다음 프로세스를 수행합니다.

쿼리에 대해 적절한 계획이 생성되지만 쿼리의 논리적 의미는 변경되지 않도록 조인 순서를 변경하고 FORCE ORDER를 사용하고 조인 힌트, 인덱스 힌트 및 기타 기술을 사용하여 쿼리를 수정하는 방법을 결정합니다.
원래 쿼리와 같은 방법으로, 즉 sp_executesql 또는 sp_cursorprepexec를 사용하거나 독립 실행형 일괄 처리로 제출된 다시 작성한 쿼리에 대한 계획을 캡처합니다.
사용자가 얻은 적절한 계획을 원래 쿼리에 강제 적용할 수 있는 계획 지침을 만듭니다.
SQL Server 프로파일러를 사용하여 Performance 범주에서 Plan Guide Successful 및 Plan Guide Unsuccessful 이벤트를 선택한 다음 원래 쿼리를 실행합니다. 쿼리가 계획 지침을 사용 중인지 확인하려면 SQL Server 프로파일러를 검사합니다.

예

다음 쿼리가 생성한 쿼리 계획이 너무 느리다고 가정합니다.

USE AdventureWorks2008R2;
GO
EXEC sp_executesql 
@stmt = N'SELECT soh.SalesPersonID
   ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
   ,e.JobTitle
   ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson AS sp 
    INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee AS e ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

다음과 같이 논리적으로는 같지만 조인 순서가 다른 강제 적용할 쿼리를 다시 작성할 수 있습니다.

USE AdventureWorks2008R2;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson sp 
    INNER JOIN Sales.SalesOrderHeader soh ON sp.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1
    OPTION (FORCE ORDER)', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

원래 쿼리에 다시 작성한 쿼리에 대한 계획을 강제 적용하는 계획 지침을 만들려면 다음 코드에서 볼 수 있듯이 변수의 계획을 캡처하고 계획 지침 문에 변수를 지정합니다.

DBCC FREEPROCCACHE;
GO
USE AdventureWorks2008R2;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson sp 
    INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee AS e ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1
    OPTION (FORCE ORDER)', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
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'%Sales.SalesPerson%' AND st.text LIKE N'%OPTION (FORCE ORDER)%');

EXEC sp_create_plan_guide
@name = N'ForceOrderGuide1',
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson AS sp 
    INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory ASst ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee ASe ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person ASp ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1', 
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@p1 nvarchar(80)',
@hints = @xml_showplan;

GO
SELECT * FROM sys.plan_guides;
GO

원래 쿼리를 실행하기 전에 SQL Server 프로파일러를 사용하여 추적을 만들고 Performance 범주에서 Plan Guide Successful 및 Plan Guide Unsuccessful 이벤트를 선택합니다. 원래 쿼리를 실행하고 추적 출력에서 쿼리 결과를 확인합니다.

USE AdventureWorks2008R2;
GO
EXEC sp_executesql 
@stmt = N'SELECT
    soh.SalesPersonID
    ,p.FirstName + '' '' + COALESCE(p.MiddleName, '''') + '' '' + p.LastName AS FullName
    ,e.JobTitle
    ,st.Name AS SalesTerritory
    ,soh.SubTotal
    ,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear 
    FROM Sales.SalesPerson AS sp 
    INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID
    INNER JOIN Sales.SalesTerritory ASst ON sp.TerritoryID = st.TerritoryID
    INNER JOIN HumanResources.Employee ASe ON soh.SalesPersonID = e.BusinessEntityID 
    INNER JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID
    WHERE st.[Group] = @p1', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

참고 항목

다음을 통해 공유

계획 적용 시나리오: 다시 작성한 쿼리에서 얻은 계획을 적용할 계획 지침 만들기

예

참고 항목

태스크

참조

개념

관련 자료

추가 리소스