1. 首页
  2. 博客
  3. SQL Server 中的强制参数化如何影响筛选索引

SQL Server 中的强制参数化如何影响筛选索引

问题 强制参数化是 SQL Server 中的一项重要功能，它通过不对谓词文字的每个组合引入新计划来减少计划缓存膨胀。如果您阅读 Brady Upton 的关于简单参数化和强制参数化之间的根本区别的提示，他会提到强制可能会误导您的一个领域：重复使用相同的计划，即使由于数据倾斜不同的参数值本应产生不同的计划. 但还涉及另一个潜在成本：如果您使用 过滤索引，这些索引可能会变得无用。

解决方案 一个 过滤索引可以为特定类型的查询，对已知值使用谓词非常方便（虽然我承认 他们是不完美）。您可能只对标记为过期的图书馆书籍或不活动的客户使用过滤索引，或者为查询频率较高的特定业务时段（想想母亲节前一周的花店）构建临时过滤索引。过滤索引是有益的，因为它不必为基表中的每一行存储一个索引行——它只关心那些匹配过滤器的行。如果图书馆有 500,000 本书，但只有 200 本书被标记为过期，则索引只需维护这 200 行，因此读取该索引将比处理表中的所有行更有效。

让我们举一个简单的例子，我们有一堆潜在的状态值——0、1、2 等。 但是有一种特殊情况，当一行被标记为 status = 255 时，这种情况很少发生，但是我们经常查询这些行，因此我们将创建一个过滤索引。

CREATE DATABASE aw;
GO
USE aw;
GO

CREATE TABLE dbo.what
(
  id int NOT NULL IDENTITY(1,1), name sysname, status tinyint,
  CONSTRAINT PK_what PRIMARY KEY(id)
);

-- distribute status values 0, 1, 2 (for me, ~11K rows, YMMV):
INSERT dbo.what(name,status)
 SELECT name, ABS(object_id) % 3 FROM sys.all_columns;

-- update ~11 rows to a different status:
UPDATE dbo.what SET status=255 WHERE id % 1000 = 0;

-- create a filtered index:
CREATE INDEX filtered ON dbo.what(id,status) INCLUDE(name) WHERE status = 255;

现在观察过滤索引用于此查询：

SELECT * FROM dbo.what WHERE status = 255;

结果：

001.png

我已经用橙色手动突出显示了重点。即使该语句似乎已被参数化，查询计划仍然是为了使用过滤索引而生成的。请注意，实际行和估计行非常低（即使估计不准确），并且此扫描的预期 I/O 成本可以忽略不计。select 操作符上有一个关于不 匹配的过滤索引的警告 ，即使这显然是被选择的索引。

现在，当我们将数据库更改为使用强制参数化时会发生什么？有人可能会这样做，以阻止使用字符串文字的即席查询填充计划缓存，完全独立于过滤索引的任何知识。

ALTER DATABASE aw SET PARAMETERIZATION FORCED;

此更改需要独占访问权限，但不需要重新启动服务。它将使现有计划无效，因此当您再次运行此查询时：

SELECT * FROM dbo.what WHERE status = 255;

执行计划看起来会有些不同：

002.png

再次关注以橙色突出显示的区域：该语句有一个参数@0（以前它有@1），但更重要的是，现在扫描聚集索引而不是过滤索引。这会影响整个计划，包括估计要读取和实际读取的行数以返回这 11 行。您可以看到更高的 I/O 成本（大约 22 倍），现在在工具提示中明确列出了谓词，并且您可以看到有关剩余 I/O 的警告（这意味着读取的行比必要的多得多）。root 运算符仍然有关于不匹配索引的警告，因此至少该计划为您提供了一些线索，表明存在过滤索引，如果您更改数据库的参数化设置（或将OPTION (RECOMPILE)添加 到语句），这可能会很有用：

003.png

该警告很有用，但如上所述，即使该索引是为计划选择的索引，它也可能存在。

概括 打开强制参数化，即使它有重要的好处，也应该针对任何工作负载进行测试，尤其是那些涉及过滤索引的工作负载。在考虑参数化设置等选项以解决其他问题时，还必须检查您希望从筛选索引中受益的查询，并确保权衡任何更改的影响。

原文链接：https://codingdict.com/

热门标签