通过

  • 项目

计划强制方案：创建用于指定查询计划的计划指南

通过使用 sp_create_plan_guide 系统存储过程并在 @hints 参数中为查询指定 XML 显示计划格式的查询计划来创建计划指南时，可以强制执行查询计划。计划指南可用于在无法或不希望直接更改应用程序时对部署应用程序中的查询应用查询提示或查询计划。有关计划指南的详细信息，请参阅使用计划指南在部署的应用程序中优化查询。在这种情况下，会将一个特定的查询计划附加到计划指南中。

假设应用程序包含下面的存储过程：

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

假设大多数执行此过程的查询性能都比较差，因为查询计划并未对 @CountryRegion 参数的代表值或“最差情况”值进行优化。您希望强制此存储过程使用已针对某个特定国家或地区进行过优化的特定查询计划。但是，因为您是从独立的软件供应商处购买的应用程序，所以您无法直接更改应用程序中的存储过程。而您可以为查询创建计划指南，在计划指南中指定已针对代表值进行了优化的查询计划。

若要将查询计划附加到计划指南中，必须先在存储过程中为查询获取一个已优化的查询计划。为此，您需要执行存储过程中定义的查询，使用代表值或“最差情况”常量值替换 @CountryRegion 参数。然后，查询 sys.dm_exec_query_stats 动态管理视图以从计划缓存中获取查询计划。建议将 XML 显示计划分配给变量；否则，必须通过在单引号前面再加上一个单引号来对 XML 显示计划中的任何单引号进行转义。最后，创建一个计划指南，指定 @hints 参数中的 XML 显示计划。

示例

下面的代码示例演示了为 Sales.GetSalesOrderByCountryRegion 存储过程获取优化的查询计划以及将其附加到计划指南时所需的步骤。执行该存储过程时，过程中定义的查询与计划指南匹配，并且查询优化器使用在计划指南中指定的查询计划。

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');