Partilhar via

  • Artigo

Cenário de imposição de plano: criar um guia de plano que especifica um plano de consulta

Você pode impor um plano de consulta ao criar um guia de plano usando o procedimento armazenado de sistema sp_create_plan_guide e especificando um plano de consulta em formato de Plano de Execução XML para a consulta no parâmetro @hints. Guias de plano são usados para aplicar dicas de consulta ou planos de consulta a consultas em aplicativos implantados quando você não pode ou não deseja alterar o aplicativo diretamente. Para obter mais informações sobre guias de plano, consulte Otimizando consultas em aplicações implantadas com guias de plano. Neste cenário, você está anexando um plano de consulta específico ao guia de plano.

Suponha que seu aplicativo contém o seguinte procedimento armazenado:

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

Assuma que a maioria das consultas que executa esse procedimento tem um desempenho inadequado porque o plano de consulta não está otimizado para um valor representativo ou "pior caso" para o parâmetro @CountryRegion. Você quer impor esse procedimento armazenado a usar um plano de consulta específico que foi otimizado para um país ou região específica. Porém, você não pode alterar o procedimento armazenado diretamente no aplicativo porque você comprou o aplicativo de um fornecedor de software independente. Em vez disso, você pode criar um guia de plano para a consulta, especificando um plano de consulta no guia de plano otimizado para o valor representativo.

Para anexar um plano de consulta a um guia de plano, é necessário primeiro obter um plano de consulta otimizado para a consulta no procedimento armazenado. Isso é feito executando a consulta definida no procedimento armazenado, substituindo um valor constante representativo ou "pior caso" no lugar do parâmetro @CountryRegion. Em seguida, você consulta a exibição de gerenciamento dinâmico sys.dm_exec_query_stats para obter o plano de consulta do cache de plano. Recomendamos atribuir o Plano de Execução XML a uma variável; caso contrário, você deverá ignorar quaisquer aspas simples no Plano de Execução XML, precedendo-as com outra aspa simples. Finalmente, você cria um guia de plano especificando o Plano de Execução XML no parâmetro @hints.

Exemplo

O exemplo de código a seguir demonstra as etapas necessárias para obter um plano de consulta otimizado para o procedimento armazenado Sales.GetSalesOrderByCountryRegion e anexá-lo a um guia de plano. Quando o procedimento armazenado é executado, a consulta definida no procedimento é comparada ao guia de plano e o otimizador de consultas usa o plano de consulta especificado no guia de plano.

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');