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

Часто наиболее удобный способ получения улучшенного плана для запроса — это переписывание запроса вручную с использованием подсказок в запросах и изменением порядка объединения, алгоритмов объединения или применения индекса, не меняя при этом логическое значение запроса. Однако если запрос находится внутри развернутого приложения, этот вариант может быть недоступен. В этом случае может помочь структура плана. Структура плана присоединяет к запросам подсказки в запросах или планы запросов в случаях, когда невозможно или нежелательно изменение текста запроса напрямую. Дополнительные сведения см. в разделе Оптимизация запросов в используемых приложениях с помощью структур планов.

Чтобы вручную переписать запрос, захватите для него план и примените этот план к исходному запросу, добавив структуру плана, содержащую захваченный план. Выполните следующие действия:

  1. определите, как следует модифицировать запрос (путем изменения порядка соединения, использования FORCE ORDER, использования подсказок в соединении и подсказок индекса, а также других способов), чтобы создать для запроса хороший план, не изменив его логическое значение;

  2. захватите план для переписанного запроса, переданного так же, как и исходный запрос (например, с помощью процедур sp_executesql, sp_cursorprepexec или как изолированный пакет);

  3. создайте структуру плана для принудительного выполнения полученного для исходного запроса хорошего плана.

  4. C помощью приложения Приложение SQL Server Profiler выберите события Plan Guide Successful и Plan Guide Unsuccessful в категории Performance и выполните исходный запрос. Проверьте приложение Приложение SQL Server Profiler, чтобы убедиться, что в запросе используется структура плана.

Пример

Предположим, что следующий запрос формирует слишком медленный план запроса:

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

Запрос перезаписывается следующим образом, чтобы логически он не менялся, но использовал другой порядок соединения, который внедряется принудительно.

USE AdventureWorks;
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
    OPTION (FORCE ORDER)', 
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO

Чтобы создать структуру плана, применяющую план перезаписанного запроса к исходному запросу, захватите план в переменной и укажите переменную в инструкции структуры плана, как показано в следующем коде.

DBCC FREEPROCCACHE;
GO
USE AdventureWorks;
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
    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
    ,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 = @xml_showplan;

GO
SELECT * FROM sys.plan_guides;
GO

Перед выполнением исходного запроса создайте трассировку с помощью приложения Приложение SQL Server Profiler и выберите события Plan Guide Successful и Plan Guide Unsuccessful в категории Performance. Выполните исходный запрос и проверьте результаты запроса в выходных данных трассировки.

USE AdventureWorks;
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
  • Last updated on