Compartilhar via


Cenário de imposição de plano: criar um guia de plano para impor um plano obtido de uma consulta regravada

Frequentemente, o modo mais conveniente de obter um plano aprimorado para uma consulta é reescrever a consulta manualmente para forçar a ordem da junção, os algoritmos de junção ou uso de índice usando dicas de consulta, sem alterar o significado lógico da consulta. No entanto, se a consulta estiver dentro de um aplicativo implantado esse método pode não estar disponível. O uso de guias de plano pode ajudar nessa situação. Guias de plano funcionam anexando dicas ou planos de consulta a consultas quando não é possível ou desejável alterar o texto de uma consulta diretamente. Para obter mais informações, consulte Otimizando consultas em aplicações implantadas com guias de plano.

Para reescrever manualmente uma consulta, capture o plano para ela, aplique-o à consulta original com um guia de plano que contém o plano capturado e siga este processo:

  1. Determine como modificar a consulta (alterando a ordem da junção, usando FORCE ORDER, dicas de junção ou dicas de índice e aplicando outras técnicas) para que um bom plano seja produzido, mas o significado lógico da consulta não seja alterado.

  2. Capture o plano para a consulta regravada, enviada da mesma forma que a consulta original (usando, por exemplo, sp_executesql, sp_cursorprepexec ou como um lote autônomo).

  3. Crie uma guia de plano para impor o bom plano que você obteve na consulta original.

  4. Usando o SQL Server Profiler, selecione os eventos Plan Guide Successful e Plan Guide Unsuccessful na categoria Performance e execute a consulta original. Examine SQL Server Profiler para verificar se a consulta está usando o guia de plano.

Exemplo

Suponha que a seguinte consulta gere um plano de consulta muito lento:

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

Você pode regravar a consulta como segue para ser logicamente semelhante, mas com uma ordem de junção diferente, que é imposta.

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

Para criar um guia de plano para impor o plano para a consulta regravada na consulta original, capture o plano em uma variável e especifique a variável na instrução de guia de plano, como mostrado no código a seguir.

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

Antes de executar a consulta original, crie um rastreamento usando SQL Server Profiler e selecione os eventos Plan Guide Successful e Plan Guide Unsuccessful na categoria Performance. Execute a consulta original e verifique os resultados da consulta na saída do rastreamento.

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