• Artículo

Escenario para forzar planes: crear una guía de plan que especifica un plan de consulta

Puede forzar un plan de consulta durante la creación de una guía de plan utilizando el procedimiento almacenado del sistema de sp_create_plan_guide y especificando un plan de consulta en formato plan de presentación XML para la consulta en el parámetro @hints. Las guías de plan se utilizan para aplicar sugerencias de consulta o planes de consulta a las consultas en aplicaciones implementadas cuando no se puede o no se desea cambiar la aplicación directamente. Para obtener más información acerca de las guías de plan, vea Optimizar consultas en aplicaciones implementadas mediante guías de plan. En este escenario, se adjunta un plan de consulta específico a la guía de plan.

Suponga que la aplicación incluye el procedimiento almacenado siguiente:

USE AdventureWorks2008R2;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))
AS
BEGIN
    SELECT h.SalesOrderID, h.OrderDate, h.Comment
    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 = @CountryRegion;
END;
GO

Supongamos que la mayoría de las consultas que ejecutan este procedimiento tienen un rendimiento bajo porque el plan de consulta no está optimizado para un valor representativo o del tipo "el peor de los casos" para el parámetro @CountryRegion. Desea obligar a este procedimiento almacenado a utilizar un plan de consulta concreto que se ha optimizado para un país o región determinados. Sin embargo, no puede cambiar directamente el procedimiento almacenado en la aplicación porque la compró a un fabricante de software independiente. No obstante, puede crear una guía de plan para la consulta, especificando un plan de consulta en la guía de plan que se ha optimizado para el valor representativo.

Para asociar un plan de consulta a una guía de plan, primero debe obtener un plan de consulta optimizado para la consulta en el procedimiento almacenado. Esto se hace ejecutando la consulta definida en el procedimiento almacenado, pero sustituyendo el parámetro @CountryRegion por un valor constante representativo o del tipo "el peor de los casos". A continuación, se consulta la vista de administración dinámica sys.dm_exec_query_stats para obtener el plan de consulta de la caché del plan. Se recomienda asignar el plan de presentación XML a una variable; de lo contrario, cada comilla sencilla existente en el plan de presentación deberá utilizar un carácter de escape (deberá anteponerle otra comilla sencilla). Por último, se crea una guía de plan especificando el plan de presentación XML en el parámetro @hints.

Ejemplo

En el ejemplo de código siguiente se muestran los pasos necesarios para obtener un plan de consulta optimizado para el procedimiento almacenado Sales.GetSalesOrderByCountryRegion y asociarlo a una guía de plan. Cuando se ejecuta el procedimiento almacenado, se hace coincidir la consulta definida en el procedimiento con la guía de plan, y el optimizador de consultas utiliza el plan de consulta especificado en la guía de plan.

CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))
AS
BEGIN
    SELECT h.SalesOrderID, h.OrderDate, h.Comment
    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 = @CountryRegion;
END;
GO
-- Execute the query based on a representative or "worst-case" scenario.
GO
SELECT h.SalesOrderID, h.OrderDate, h.Comment
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 = N'US';
GO
-- Retrieve the query plan for the previous query. Assign the query plan to a variable and attach the query plan to a plan guide.
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 h.SalesOrderID, h.OrderDate, h.Comment
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 = N''US'';%');

EXEC sp_create_plan_guide 
    @name = N'Guide_for_GetSalesByCountryRegion',
    @stmt = N'SELECT h.SalesOrderID, h.OrderDate, h.Comment
    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 = @CountryRegion',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountryRegion',
    @params = NULL,
    @hints = @xml_showplan;
GO
SELECT * FROM sys.plan_guides
WHERE scope_object_id = OBJECT_ID(N'Sales.GetSalesOrderByCountryRegion');