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

  • Статья

Сценарий форсирования планов. Создание структуры плана, задающей план запроса

Можно форсировать план запроса при создании структуры плана с помощью системной хранимой процедуры sp_create_plan_guide, указав план запроса в формате XML Showplan в параметре @hints. Структуры планов используются для применения подсказок в запросе или планов запроса к запросам развертываемых приложений, когда нельзя изменить приложение напрямую. Дополнительные сведения о структурах планов см. в разделе Оптимизация запросов в используемых приложениях с помощью структур планов. В этом случае конкретный план запроса присоединяется к структуре плана.

Пусть приложение содержит следующую хранимую процедуру:

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))
AS
BEGIN
    SELECT h.SalesOrderID, h.OrderDate, h.Comment
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
    WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion;
END;
GO

Предположим, большинство запросов, запускающих данную процедуру, имеют низкую производительность, потому что план запроса не оптимизирован для типового значения или значения «худшего случая» для параметра @CountryRegion. Можно попробовать принудительно использовать в данной хранимой процедуре специальный план запроса, оптимизированный для конкретной страны или региона. Однако напрямую изменить хранимую процедуру в приложении нельзя, так как приложение приобретено у независимого поставщика программного обеспечения. Вместо этого можно создать структуру плана для запроса, указав план запроса в структуре плана, которая была оптимизирована для типового значения.

Чтобы присоединить план запроса к структуре плана, сначала нужно получить оптимизированный план запроса для запроса в хранимой процедуре. Для этого надо выполнить запрос, определенный в хранимой процедуре, подставив константное типичное значение или значение «худшего случая» вместо параметра @CountryRegion. После этого следует выполнить запрос к динамическому административному представлению sys.dm_exec_query_stats, чтобы получить план запроса из кэша планов. Значение аргумента XML Showplan рекомендуется присвоить переменной, иначе каждый символ одиночной кавычки необходимо предварять дополнительным символом одиночной кавычки. Наконец, можно создать структуру плана, указав аргумент XML Showplan в параметре @hints.

Пример

В следующем примере кода демонстрируются шаги по получению оптимизированного плана запроса для хранимой процедуры Sales.GetSalesOrderByCountryRegion и присоединению его к структуре плана. При выполнении хранимой процедуры запрос, определенный в процедуре, соотносится со структурой плана, и оптимизатор запросов использует план запроса, указанный в структуре плана.

CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))
AS
BEGIN
    SELECT h.SalesOrderID, h.OrderDate, h.Comment
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
    WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion;
END;
GO
-- Execute the query based on a representative or "worst-case" scenario.
GO
SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = N'US';
GO
-- Retrieve the query plan for the previous query. Assign the query plan to a variable and attach the query plan to a plan guide.
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'SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = N''US'';%');

EXEC sp_create_plan_guide 
    @name = N'Guide_for_GetSalesByCountryRegion',
    @stmt = N'SELECT h.SalesOrderID, h.OrderDate, h.Comment
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
    WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountryRegion',
    @params = NULL,
    @hints = @xml_showplan;
GO
SELECT * FROM sys.plan_guides
WHERE scope_object_id = OBJECT_ID(N'Sales.GetSalesOrderByCountryRegion');