Сценарий форсирования планов: создайте руководство плана для принудительного исполнения плана, полученного из перезаписанного запроса

Статья
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 …'')'

Убедитесь, что в плане XML-запроса не осталось одиночных апострофов ('), заменив их на четыре одиночных апострофа (''''). После этого подставьте план запроса в строку @hints. Это необходимо, так как план запроса вложен между двумя строковыми литералами.

См. также

Поделиться через

Сценарий форсирования планов: создайте руководство плана для принудительного исполнения плана, полученного из перезаписанного запроса

Пример

См. также

Задачи

Основные понятия

Другие ресурсы

Справка и поддержка

Дополнительные ресурсы