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:
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.
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).
Crie uma guia de plano para impor o bom plano que você obteve na consulta original.
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
Consulte também