使用计划指南指定查询参数化行为

当 PARAMETERIZATION 数据库选项设置为 SIMPLE 时，SQL Server 查询优化器可以选择参数化查询。 这意味着查询中包含的任何文本值都替换为参数。 此过程称为简单参数化。 当 SIMPLE 参数化生效时，无法控制哪些查询已参数化，哪些查询不是参数化查询。 但是，可以通过将 PARAMETERIZATION 数据库选项设置为 FORCED 来指定数据库中的所有查询进行参数化。 此过程称为强制参数化。

可以通过以下方式使用计划指南替代数据库的参数化行为：

• 当 PARAMETERIZATION 数据库选项设置为 SIMPLE 时，可以指定对特定类查询尝试强制参数化。 为此，请在查询的参数化形式上创建 TEMPLATE 计划指南，并在 sp_create_plan_guide 存储过程中指定 PARAMETERIZATION FORCED 查询提示。 可以将此类计划指南视为仅对特定类查询（而不是所有查询）启用强制参数化的方法。

• 将 PARAMETERIZATION 数据库选项设置为 FORCED 时，可以指定对于特定类查询，只尝试简单的参数化，而不是强制参数化。 为此，可以针对查询的强制参数化形式创建 TEMPLATE 计划指南，并在 sp_create_plan_guide中指定 PARAMETERIZATION SIMPLE 查询提示。

请考虑对 AdventureWorks2012 数据库进行以下查询：

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

作为数据库管理员，你已确定不想对数据库中的所有查询启用强制参数化。 但是，你确实希望避免所有在语法上等效于上一查询的查询的编译成本，但只与其常量文本值不同。 换句话说，需要对查询进行参数化，以便重复使用此类查询的查询计划。 在这种情况下，请完成以下步骤：

1. 检索查询的参数化形式。 获取此值以用于 sp_create_plan_guide 的唯一安全方法是使用 sp_get_query_template 系统存储过程。

2. 针对查询的参数化形式创建计划指南，指定 PARAMETERIZATION FORCED 查询提示。

   重要

   作为参数化查询的一部分，SQL Server 将数据类型分配给替换文本值的参数，具体取决于文本的值和大小。 相同的过程也适用于传递给sp_get_query_template的@stmt输出参数的常量字面值。 由于sp_create_plan_guide@params参数中指定的数据类型必须与 SQL Server 参数化时查询的数据类型匹配，因此可能需要创建多个计划指南来涵盖查询可能的参数值的完整范围。

以下脚本可用于获取参数化查询，然后为其创建计划指南：

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 AS pm   
      INNER JOIN Production.ProductInventory AS 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;  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION(PARAMETERIZATION FORCED)';  

同样，在已启用强制参数化的数据库中，可以确保示例查询和语法上等效的其他查询（除了常量文本值）根据简单参数化规则参数化。 为此，请在 OPTION 子句中指定 PARAMETERIZATION SIMPLE 而不是 PARAMETERIZATION FORCED。