通过

  • 项目

设计参数化查询的计划指南

可以为参数化的查询创建计划指南。出于以下原因之一，就可以使查询参数化：

  • 使用 sp_executesql 提交查询。

  • 在数据库中启用强制参数化。这将使所有合格的查询参数化。

  • 已对此查询所属的一类查询创建了单独的计划指南，指定应将这些查询参数化。

当对参数化的查询创建计划指南时，实质上是在创建将所有查询参数化为同一格式的计划指南。这些查询只是常量文字值不同。例如，在启用强制参数化的数据库中，下列两个查询将参数化为同一格式：

SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID 
WHERE pi.ProductID = 101 
GROUP BY pi.ProductID, pi.Quantity 
HAVING sum(pi.Quantity) > 50;

SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID 
WHERE pi.ProductID = 101 
GROUP BY pi.ProductID, pi.Quantity 
HAVING sum(pi.Quantity) > 100;

若要为参数化的查询创建计划指南，请创建类型为 SQL 的计划指南并指定 sp_create_plan_guide 存储过程中的查询参数化格式。

例如，若要获取上一示例中其中一个查询的参数化格式并在其上创建计划指南以强制优化器使用哈希联接，请按以下步骤操作：

  1. 通过执行 sp_get_query_template 来获取查询的参数化格式。

  2. 如果 SQL Server 还未使用 sp_executesql 或 PARAMETERIZATION FORCED 数据库的 SET 选项使查询参数化，请创建类型为 TEMPLATE 的计划指南以强制参数化。

  3. 对参数化查询创建类型 SQL 的计划指南。

以下批处理执行所有的这三个步骤：

--Obtain the paramaterized form of the query:
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total 
    FROM Production.ProductModel pm 
    INNER JOIN Production.ProductInventory pi 
        ON pm.ProductModelID = pi.ProductID 
    WHERE pi.ProductID = 101 
    GROUP BY pi.ProductID, pi.Quantity 
    HAVING SUM(pi.Quantity) > 50',
@stmt OUTPUT, 
@params OUTPUT;
--Force parameterization of the query. (This step is only required
--if the query is not already being parameterized.)
EXEC sp_create_plan_guide N'TemplateGuide1', 
    @stmt, 
N'TEMPLATE', 
NULL, 
@params, 
N'OPTION(PARAMETERIZATION FORCED)';
--Create a plan guide on the parameterized query
EXEC sp_create_plan_guide N'GeneralizedGuide1', 
@stmt, 
N'SQL', 
NULL, 
@params, 
N'OPTION(HASH JOIN)';

此时计划指南将应用于所有参数化为指定的格式，但包含不同常量文字值的查询。