• Artículo

Descripción de las guías de plan

Este tema describe las guías de plan y explica cómo se pueden utilizar para optimizar el rendimiento de las consultas cuando no pueda o no desee cambiar directamente el texto de dichas consultas. 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 adjuntando sugerencias de consulta o un plan de consulta fijo para ellas. En la guía de plan, se especifica la instrucción de Transact-SQL que se desea optimizar y además una cláusula OPTION que incluye las sugerencias de consulta que se desean utilizar o un plan de consulta específico con el que desea optimizar la consulta. Cuando la consulta se ejecuta, el SQL Server hace coincidir la instrucción de Transact-SQL con la guía de plan y además asocia en tiempo de ejecución la cláusula OPTION a la consulta o utiliza el plan de consultas especificado.

Nota

Aunque las guías de plan solo se pueden utilizar en las versiones Standard, Developer, Evaluation y Enterprise de SQL Server sí se pueden ver en todas las versiones. También se pueden adjuntar bases de datos que incluyen guías de plan a cualquier versión. Las guías de plan permanecen intactas cuando se restaura o adjunta una base de datos a una versión actualizada de SQL Server 2008.

Comparar guías de plan con consultas

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

  • Una guía de plan OBJECT compara las consultas que se ejecutan en el contexto de procedimientos almacenados de Transact-SQL, funciones escalares definidas por el usuario, funciones definidas por el usuario con valores de tabla de múltiples instrucciones y desencadenadores DML.

  • Una guía de plan de SQL compara 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.

  • Una guía de plan TEMPLATE compara 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. Para obtener más información, vea Parametrización simple y Parametrizaciones forzadas.

Para obtener más información, vea Cómo asocia SQL Server las guías de plan a consultas.

Guías de plan OBJECT

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

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

Asuma que este procedimiento almacenado ha sido compilado y optimizado para @Country_region = N'AU' (Australia). Sin embargo, dado hay relativamente pocos pedidos de ventas que se originan en Australia, el rendimiento se ve reducido cuando la consulta ejecuta utilizando valores para los parámetro que se corresponden con países con más pedidos de ventas. Dado que el mayor número de pedidos de ventas se origina en Estados Unidos, el rendimiento de un plan de consulta generado para @Country_region = N'US' será probablemente mejor para todos los valores posibles del parámetro @Country_region.

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 AdventureWorks2008R2.

sp_create_plan_guide 
@name = N'Guide1',
@stmt = N'SELECT *FROM Sales.SalesOrderHeader AS h,
        Sales.Customer AS c,
        Sales.SalesTerritory AS t
        WHERE h.CustomerID = c.CustomerID 
            AND c.TerritoryID = t.TerritoryID
            AND CountryRegionCode = @Country_region',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetSalesOrderByCountry',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country_region = 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'').

Guías de plan SQL

Las guías de plan de SQL se aplican a las instrucciones y lotes independientes de Transact-SQL. Con frecuencia, las aplicaciones envían esas instrucciones utilizando el procedimiento almacenado del sistema sp_executesql. Considere, por ejemplo, el siguiente lote independiente:

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

Para evitar que se genere un plan de ejecución paralelo en esta consulta, cree la siguiente guía de plan y establezca la sugerencia de consulta MAXDOP en 1 en el parámetro @hints.

sp_create_plan_guide 
@name = N'Guide2', 
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader 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.

Nota importanteImportante

Los valores que se proporcionan para los argumentos @params y @module_or_batch de la instrucción sp_create_plan guide deben coincidir con el texto correspondiente enviado en la consulta real. Para obtener más información, vea sp_create_plan_guide (Transact-SQL) y Usar SQL Server Profiler 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.

En el ejemplo siguiente se crea una guía de plan que coincida con cualquier consulta que se parametrice de una forma específica, e indica a SQL Server que fuerce la parametrización de la consulta. Las dos consultas siguientes son equivalentes desde el punto de vista sintáctico, pero se diferencian sólo en los valores literales de las constantes.

SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;

SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
    ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;

Ésta es una guía de plan creada en la consulta con parámetros:

EXEC sp_create_plan_guide 
    @name = N'TemplateGuide1',
    @stmt = N'SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
              INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
                  ON h.SalesOrderID = d.SalesOrderID
              WHERE h.SalesOrderID = @0',
    @type = N'TEMPLATE',
    @module_or_batch = NULL,
    @params = N'@0 int',
    @hints = N'OPTION(PARAMETERIZATION FORCED)';

En el ejemplo anterior, el valor del parámetro @stmt representa la forma de la consulta con parámetros. La única manera confiable de obtener este valor para utilizarlo en sp_create_plan_guide es utilizar el procedimiento almacenado del sistema sp_get_query_template. El script siguiente se puede utilizar para obtener la consulta con parámetros y, después crear una guía de plan basada en ella.

DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template 
    N'SELECT * FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS h
      INNER JOIN AdventureWorks2008R2.Sales.SalesOrderDetail AS d 
          ON h.SalesOrderID = d.SalesOrderID
      WHERE h.SalesOrderID = 45639;',
    @stmt OUTPUT, 
    @params OUTPUT
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
    N'TEMPLATE', 
    NULL, 
    @params, 
    N'OPTION(PARAMETERIZATION FORCED)';
Nota importanteImportante

El valor de los literales de constante del parámetro @stmt pasado a sp_get_query_template podría afectar al tipo de datos elegido para el parámetro que reemplaza al valor literal. Esto afectaría a la correspondencia de la guía de plan. Puede que tenga que crear más de una guía de plan para abarcar los distintos intervalos de valores de parámetros.

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.

Aplicar un plan de consultas fijo a una guía de plan

Puede aplicar un plan de consultas fijo a una guía de plan del tipo OBJECT o SQL. Las guías de plan que se aplican a un plan de consultas fijo resultan útiles cuando ya se tiene constancia de un plan de ejecución existente cuyo rendimiento es mejor que el del seleccionado por el optimizador para una consulta determinada.

El ejemplo siguiente crea una guía de plan para una instrucción SQL ad hoc sencilla. El plan de consultas deseado para esta instrucción se proporciona en la guía de plan si se especifica el plan de presentación XML para la consulta directamente en el parámetro @hints . El ejemplo ejecuta primero la instrucción SQL para generar un plan en la memoria caché del plan. Para los fines de este ejemplo, se supone que el plan generado es el plan deseado y que no se requiere ninguna optimización adicional de la consulta. El plan de presentación XML para la consulta se obtiene consultando las vistas de administración dinámica sys.dm_exec_query_stats, sys.dm_exec_sql_text y sys.dm_exec_text_query_plan y está asignado a la variable @xml_showplan. A continuación, la variable @xml_showplan se pasa a la instrucción sp_create_plan_guide en el parámetro @hints. O bien, puede crear una guía de plan a partir de un plan de consulta de la memoria caché del plan mediante el procedimiento almacenado sp_create_plan_guide_from_handle.

USE AdventureWorks2008R2;
GO
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
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'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');

EXEC sp_create_plan_guide 
    @name = N'Guide1_from_XML_showplan', 
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;', 
    @type = N'SQL',
    @module_or_batch = NULL, 
    @params = NULL, 
    @hints = @xml_showplan;
GO

Validar guías de planes tras una actualización

Se recomienda volver a evaluar y probar las definiciones de guías de plan al actualizar la aplicación a una nueva versión de SQL Server. Los requisitos de optimización del rendimiento y el comportamiento de la coincidencia de las guías de plan pueden cambiar. Aunque una guía de plan no válida no hará que una consulta provoque un error, el plan se compilada sin utilizar la guía de plan y posiblemente no sea la mejor opción. Después de actualizar una base de datos a SQL Server 2008, es recomendable que realice las tareas siguientes: