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

Artículo
12/15/2008

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. En este caso, la utilización de guías de plan junto con la sugerencia de consulta USE PLAN puede resultar útil. Las guías de plan funcionan adjuntando sugerencias 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 una sugerencia USE PLAN, 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 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).
Modifique una copia de la consulta original adjuntando una sugerencia de consulta OPTION (USE PLAN) que contenga el plan capturado y compruebe si puede exigir el plan capturado en la consulta.
Si la prueba provoca un error, vuelva a escribir la consulta de otro modo o bien depúrela hasta que obtenga un plan apropiado que se pueda exigir en la consulta original.
Cree una guía de plan para exigir el plan apropiado obtenido en la consulta original.

Ejemplo

Suponga que la consulta siguiente genera un plan de consulta demasiado lento:

USE AdventureWorks;
GO
SET STATISTICS XML ON;
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
SET STATISTICS XML OFF;
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
SET STATISTICS XML ON;
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].[SalesTerritory] st -- Moved this join earlier 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [Sales].[SalesOrderHeader] soh 
    ON sp.[SalesPersonID] = soh.[SalesPersonID]
    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)',  -- force join order to be as specified in FROM list
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
SET STATISTICS XML OFF
GO

Tras capturar el plan de consulta STATISTICS XML para la consulta que se ha vuelto a escribir y probarla en la consulta original, cree una guía de plan para exigir el plan en la consulta original, tal como se muestra en el código siguiente:

EXEC sp_create_plan_guide
@name = N'ForceOrderGuide',
@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 = N'OPTION (USE PLAN 
N''… put XML showplan for modified query here …'')'

Asegúrese de agregar caracteres de escape a las comillas simples (') del plan de consulta XML utilizando cuatro comillas simples ('''') antes de sustituir el plan de consulta en la cadena @hints. El motivo es que el plan de consulta está anidado en dos literales de cadena.

Vea también

Compartir a través de

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

Ejemplo

Vea también

Tareas

Conceptos

Otros recursos

Ayuda e información

Recursos adicionales