Сценарий форсирования планов. Создание структуры плана для принудительного выполнения плана, полученного из перезаписанного запроса
Часто наиболее удобный способ получения улучшенного плана для запроса — это переписывание запроса вручную с использованием подсказок в запросах и изменением порядка соединения, алгоритмов объединения или применения индекса, не меняя при этом логическое значение запроса. Однако если запрос находится внутри развернутого приложения, этот вариант может быть недоступен. В этом случае может помочь структура плана. Структура плана присоединяет к запросам подсказки в запросах или планы запросов в случаях, когда невозможно или нежелательно изменение текста запроса напрямую. Дополнительные сведения см. в разделе Оптимизация запросов в используемых приложениях с помощью структур планов.
Чтобы вручную перезаписать запрос, захватите для него план и примените этот план к исходному запросу, добавив структуру плана, содержащую захваченный план. Сделайте следующее:
определите, как следует модифицировать запрос (путем изменения порядка соединения, использования FORCE ORDER, использования подсказок в соединении и подсказок индекса, а также других способов), чтобы создать для запроса хороший план, не изменив его логическое значение;
захватите план для перезаписанного запроса, выполненного так же, как и оригинальный запрос (например, с помощью процедур sp_executesql, sp_cursorprepexec или как изолированный пакет);
создайте структуру плана для применения полученного для исходного запроса хорошего плана.
с помощью приложения Приложение SQL Server Profiler выберите события Plan Guide Successful и Plan Guide Unsuccessful в категории Performance и выполните исходный запрос. Проверьте приложение Приложение SQL Server Profiler, чтобы убедиться, что в запросе используется структура плана.
Пример
Предположим, что следующий запрос формирует слишком медленный план запроса:
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 Profiler и выберите события Plan Guide Successful и Plan Guide Unsuccessful в категории Performance. Выполните исходный запрос и проверьте результаты запроса в выходных данных трассировки.
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
См. также