Escenario para forzar planes: crear una guía de plan para forzar un plan obtenido a partir de una consulta que se ha vuelto a escribir
A menudo, la forma más apropiada de obtener un plan mejorado para una consulta consiste en volverla a escribir manualmente para forzar el orden de combinación, los algoritmos de combinación o el uso de índices mediante sugerencias de consulta, sin cambiar el significado lógico de la consulta. Sin embargo, si la consulta se encuentra en una aplicación implementada, puede que este método no esté disponible. El uso de las guías de plan puede resultar útil en esta situación. Las guías de plan funcionan adjuntando sugerencias de consulta o planes de consulta a las consultas cuando no se puede o no se desea cambiar el texto de una consulta directamente. Para obtener más información, vea Optimizar consultas en aplicaciones implementadas mediante guías de plan.
Para volver a escribir una consulta manualmente, capture su plan y, a continuación, aplíquelo a la consulta original con una guía de plan que incluya el plan capturado, siguiendo el proceso que se expone a continuación:
Determine cómo modificar la consulta (cambiando el orden de combinación, mediante FORCE ORDER, sugerencias de combinación, sugerencias de índice u otras técnicas), de forma que se cree un plan apropiado para la consulta, pero sin cambiar su significado lógico.
Capture el plan para la consulta que se ha vuelto a escribir, enviado simplemente como la consulta original (por ejemplo, mediante sp_executesql, sp_cursorprepexec o como un lote independiente).
Cree una guía de plan para exigir el plan apropiado obtenido en la consulta original.
Con SQL Server Profiler, seleccione los eventos Guía de plan correcta y Guía de plan incorrecta de la categoría Rendimiento y, a continuación, ejecute la consulta original. Examine SQL Server Profiler para comprobar que la consulta está utilizando la guía de plan.
Ejemplo
Suponga que la consulta siguiente genera un plan de consulta demasiado lento.
USE AdventureWorks;
GO
EXEC sp_executesql
@stmt = N'SELECT
soh.SalesPersonID
,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
,e.Title
,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.SalesPersonID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID
INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
WHERE st.[Group] = @p1',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
Puede volver a escribir la consulta tal como se muestra a continuación para que su lógica sea la misma pero con otro orden de combinación exigido.
USE AdventureWorks;
GO
EXEC sp_executesql
@stmt = N'SELECT
soh.SalesPersonID
,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
,e.Title
,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.SalesPersonID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID
INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
WHERE st.[Group] = @p1
OPTION (FORCE ORDER)',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
Para crear una guía de plan que fuerce el plan para la consulta que se ha vuelto a escribir en la consulta original, capture el plan en una variable y especifique la variable en la instrucción de guía de plan como se muestra en el código siguiente.
DBCC FREEPROCCACHE;
GO
USE AdventureWorks;
GO
EXEC sp_executesql
@stmt = N'SELECT
soh.SalesPersonID
,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
,e.Title
,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.SalesPersonID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID
INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
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
,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
,e.Title
,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.SalesPersonID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID
INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
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 ejecutar la consulta original, cree un seguimiento con SQL Server Profiler y seleccione los eventos Guía de plan correcta y Guía de plan incorrecta de la categoría Performance. Ejecute la consulta original y compruebe los resultados de la consulta en la salida del seguimiento.
USE AdventureWorks;
GO
EXEC sp_executesql
@stmt = N'SELECT
soh.SalesPersonID
,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
,e.Title
,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.SalesPersonID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID
INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
WHERE st.[Group] = @p1',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
Vea también