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

아티클
12/15/2008

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

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

쿼리에 대해 적절한 계획이 생성되지만 쿼리의 논리적 의미는 변경되지 않도록 조인 순서를 변경하고 FORCE ORDER를 사용하고 조인 힌트, 인덱스 힌트 및 기타 기술을 사용하여 쿼리를 수정하는 방법을 결정합니다.
원래 쿼리와 같은 방법으로, 즉 sp_executesql 또는 sp_cursorprepexec를 사용하거나 독립 실행형 일괄 처리로 제출된 다시 작성한 쿼리에 대한 계획을 캡처합니다.
캡처한 계획이 포함되어 있는 OPTION (USE PLAN) 쿼리 힌트 절을 연결하여 원래 쿼리의 복사본을 수정하고 캡처한 계획을 쿼리에 강제 적용할 수 있는지 여부를 테스트합니다.
테스트가 실패하면 다른 쿼리를 다시 작성해 보거나 원래 쿼리에 강제 적용할 수 있는 적절한 계획을 얻을 때까지 디버깅합니다.
사용자가 얻은 적절한 계획을 원래 쿼리에 강제 적용할 수 있는 계획 지침을 만듭니다.

예

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

USE AdventureWorks;
GO
SET STATISTICS XML ON;
GO
EXEC sp_executesql 
@stmt = N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,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.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [Sales].[SalesTerritory] st 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
SET STATISTICS XML OFF;
GO

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

USE AdventureWorks;
GO
SET STATISTICS XML ON;
GO
EXEC sp_executesql 
@stmt = N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,st.[Name] AS [SalesTerritory]
    ,soh.[SubTotal]
    ,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear] 
FROM [Sales].[SalesPerson] sp 
    INNER JOIN [Sales].[SalesTerritory] st -- Moved this join earlier 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [Sales].[SalesOrderHeader] soh 
    ON sp.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1
OPTION (FORCE ORDER)',  -- force join order to be as specified in FROM list
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
SET STATISTICS XML OFF
GO

다시 작성한 쿼리에 대한 STATISTICS XML 쿼리 계획을 캡처하고 이를 원래 쿼리에 테스트한 후 아래 코드에서 볼 수 있듯이 원래 쿼리에 계획을 강제 적용할 수 있는 계획 지침을 만듭니다.

EXEC sp_create_plan_guide
@name = N'ForceOrderGuide',
@stmt = N'SELECT 
    soh.[SalesPersonID]
    ,c.[FirstName] + '' '' + COALESCE(c.[MiddleName], '''') + '' '' + c.[LastName] AS [FullName]
    ,e.[Title]
    ,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.[SalesPersonID] = soh.[SalesPersonID]
    INNER JOIN [Sales].[SalesTerritory] st 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [HumanResources].[Employee] e 
    ON soh.[SalesPersonID] = e.[EmployeeID] 
    INNER JOIN [Person].[Contact] c 
    ON e.[ContactID] = c.ContactID
WHERE st.[Group] = @p1', 
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@p1 nvarchar(80)',
@hints = N'OPTION (USE PLAN 
N''?put XML showplan for modified query here ?')'

@hints 문자열에서 쿼리 계획을 대체하기 전에 작은따옴표 4개('''')를 사용하여 XML 쿼리 계획 내의 작은따옴표(')를 이스케이프 처리해야 합니다. 왜냐하면 쿼리 계획이 두 개의 문자열 리터럴 안에 중첩되어 있기 때문입니다.

참고 항목

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

예

참고 항목

작업

개념

관련 자료

도움말 및 정보

추가 리소스