Descripción de las guías de plan

Artículo
12/15/2008

SQL Server 2005 presenta el procedimiento almacenado del sistema sp_create_plan_guide para crear guías de plan con el fin de optimizar el rendimiento de las consultas. Este procedimiento se puede utilizar cuando no se puede o no se desea cambiar el texto de la consulta directamente. Las guías de plan pueden ser de gran utilidad cuando el rendimiento de un pequeño subconjunto de consultas de una aplicación de base de datos implementada por otro proveedor no es el esperado. Las guías de plan influyen en la optimización de las consultas adjuntándoles sugerencias. En la instrucción sp_create_plan_guide, se especifica la consulta que se desea optimizar y la cláusula OPTION que incluye las sugerencias de consulta que se desean utilizar para ese fin. Cuando se ejecuta la consulta, SQL Server busca las coincidencias entre la consulta y la guía de plan y adjunta la cláusula OPTION a la consulta en tiempo de ejecución.

[!NOTA] Las guías de plan sólo se pueden crear y utilizar en las versiones Standard, Developer, Evaluation y Enterprise de SQL Server 2005. Se pueden quitar en todas las versiones.

Las consultas que pueden aprovechar las guías de plan suelen estar basadas en parámetros, y puede que su rendimiento sea bajo porque utilizan planes de consulta en caché cuyos valores de parámetros no representan un escenario del tipo "el peor de los casos" o el más representativo. Este problema se puede solucionar con las sugerencias de consulta OPTIMIZE FOR y RECOMPILE. OPTIMIZE FOR indica a SQL Server que utilice un valor determinado para un parámetro cuando se optimiza la consulta. RECOMPILE indica al servidor que descarte un plan de consulta tras la ejecución, con lo que se forzará al optimizador de consultas a volver a compilar un nuevo plan de consulta la próxima vez que se ejecute la misma consulta.

Otra sugerencia de consulta habitual que se puede utilizar con las guías de plan es USE PLAN. Esta sugerencia se aplica cuando ya se tiene constancia de un plan de ejecución existente que se puede sustituir por el seleccionado por el optimizador para una consulta determinada porque se sabe que su rendimiento es superior. USE PLAN obliga a SQL Server a utilizar un plan de consulta determinado, especificado de forma explícita en la sintaxis de la sugerencia, al ejecutar la consulta. Una guía de plan que aplica la sugerencia de consulta USE PLAN resulta especialmente útil cuando lo más apropiado es obtener un buen plan de ejecución para una consulta volviéndola a escribir para forzar un orden de combinación, utilizar sugerencias de combinación o utilizar sugerencias de índice. Para obtener más información, vea 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.

Para obtener más información acerca de RECOMPILE, OPTIMIZE FOR, USE PLAN y otras sugerencias de consulta, vea Query Hint (Transact-SQL).

Advertencia:
Las guías de plan que utilizan incorrectamente las sugerencias de consulta pueden provocar problemas de compilación, ejecución o rendimiento. Sólo deben utilizarlas los programadores y administradores de bases de datos con experiencia.

Se pueden crear guías de plan para comparar las consultas que se ejecutan en los contextos siguientes:

Las guías de plan OBJECT comparan las consultas que se ejecutan en el contexto de procedimientos almacenados de Transact-SQL, funciones escalares, funciones con valores de tabla de múltiples instrucciones y desencadenadores DML.
Las guías de plan de SQL comparan las consultas que se ejecutan en el contexto de instrucciones independientes de Transact-SQL y lotes que no forman parte de un objeto de base de datos. Las guías de plan basadas en SQL también se pueden utilizar para comparar consultas que se parametrizan en un formulario especificado.
Las guías de plan TEMPLATE comparan consultas independientes que se parametrizan en un formulario especificado. Estas guías de plan se utilizan para reemplazar la opción PARAMETERIZATION actual de una base de datos para una clase de consultas por medio de SET.

Guías de plan OBJECT

Suponga que el siguiente procedimiento almacenado, que utiliza el parámetro @Country, existe en una aplicación de base de datos que se implementa con la base de datos AdventureWorks:

CREATE PROCEDURE Sales.GetSalesOrderByCountry (@Country nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader h, Sales.Customer c, 
        Sales.SalesTerritory t
    WHERE h.CustomerID = c.CustomerID
        AND c.TerritoryID = t.TerritoryID
        AND CountryRegionCode = @Country
END

Observa que este procedimiento almacenado ha sido compilado y optimizado para @Country = N'AU' (Australia). Sin embargo, el número de pedidos de ventas procedentes de Australia es relativamente bajo. El rendimiento disminuye cuando se ejecuta la consulta con valores de parámetros de países con más pedidos de ventas. Puesto que el país del que procede el mayor número de pedidos de ventas es Estados Unidos, el rendimiento de un plan de consulta generado para @Country=N'US' será probablemente mejor para todos los valores posibles del parámetro @Country.

Puede solucionar este problema modificando el procedimiento almacenado y agregando la sugerencia de consulta OPTIMIZE FOR a la consulta. No obstante, puesto que el procedimiento almacenado se encuentra en una aplicación implementada, no puede modificar directamente el código de la aplicación. En su lugar, puede crear la guía de plan siguiente en la base de datos AdventureWorks.

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT * FROM Sales.SalesOrderHeader h,
        Sales.Customer c,
        Sales.SalesTerritory t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))'

Cuando se ejecute la consulta especificada en la instrucción sp_create_plan_guide, se modificará la consulta antes de la optimización para incluir la cláusula OPTIMIZE FOR (@Country = N''US'') que también se especifica.

Guías de plan SQL

Las guías de plan SQL se aplican a las instrucciones y los lotes que envía con frecuencia una aplicación mediante el procedimiento almacenado del sistema sp_executesql. Considere, por ejemplo, el siguiente lote independiente:

SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC

Para evitar que se genere un plan de ejecución en paralelo en esta consulta, cree la siguiente guía de plan:

sp_create_plan_guide 
@name = N'Guide1', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader h ORDER BY OrderDate DESC',  
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)'

[!NOTA] El lote que contiene la instrucción en la que desea crear una guía de plan no puede contener una instrucción USE database.

Importante:
Los valores proporcionados para los argumentos @module_or_batch y @params de la instrucción sp_create_plan guide deben coincidir con el texto correspondiente exactamente como se envió en la consulta. Para obtener más información, vea sp_create_plan_guide (Transact-SQL) y Usar el Analizador de SQL Server para crear y probar guías de plan.

También se pueden crear guías de plan SQL en consultas que se parametrizan en el mismo formulario cuando se establece el valor de la opción SET de base de datos PARAMETERIZATION en FORCED, o cuando se crea una guía de plan TEMPLATE en la que se especifica que debe parametrizarse una clase de consultas. Para obtener más información, vea Diseñar guías de plan para consultas con parámetros.

Guías de plan TEMPLATE

Las guías de plan TEMPLATE se utilizan para reemplazar el comportamiento de parametrización de determinados formularios de consulta. Puede crear una guía de plan TEMPLATE en cualquiera de las situaciones siguientes:

Se ha establecido el valor de la opción PARAMETRIZATION de la base de datos en FORCED mediante el mandato SET, pero hay consultas que desea compilar según las reglas parametrización simple.
Se ha establecido el valor de la opción PARAMETERIZATION de la base de datos en SIMPLE (el valor predeterminado), pero desea que intente la parametrización forzada en una clase de consultas.

Para obtener más información, vea Especificar el comportamiento de parametrización de consultas por medio de guías de plan.

También puede utilizar las guías de plan TEMPLATE junto con guías de plan SQL. Por ejemplo, puede crear una guía de plan TEMPLATE para asegurarse de que se parametriza una clase de consultas. A continuación, puede crear una guía de plan SQL en el formulario parametrizado de esa consulta.

Vea también

Descripción de las guías de plan

Guías de plan OBJECT

Guías de plan SQL

Guías de plan TEMPLATE

Vea también

Conceptos

Otros recursos

Ayuda e información

Recursos adicionales