SQL Server 统计信息是系统对象, 其中包含有关索引键值中的数据分布的信息, 有时还包含常规列值. 可以在支持比较操作的任何数据类型上创建统计信息, 例如 >,<,= 等.
让我们从上一章清单 2-15 中创建的 dbo.Books 表中检查 IDX_BOOKS_ISBN 索引统计信息. 您可以使用 DBCC SHOW_STATISTICS('dbo.Books',IDX_BOOKS_ISBN) 命令来完成此操作. 结果如图 3-1 所示.
图 3-1. DBCC SHOW_STATISTICS 输出
如您所见, DBCC SHOW_STATISTICS 命令返回三个结果集. 第一个包含有关统计信息的常规元数据信息, 例如名称, 更新日期, 更新统计信息时索引中的行数等. 第一个结果集中的 "步骤" 列指示直方图中的步数 / 值的数量 (稍后将详细介绍). 查询优化程序不使用 "密度" 值, 仅出于向后兼容性目的而显示.
第二个结果集称为密度向量, 它包含有关统计 (索引) 中键值组合的密度的信息. 它是基于 1 / 不同值公式计算的, 它表示每个键值组合平均有多少行. 尽管 IDX_Books_ISBN 索引只定义了一个键列 ISBN, 但它还包含一个聚簇索引键作为索引行的一部分. 我们的表有 1,252,500 个唯一的 ISBN 值, ISBN 列的密度为 1.0 / 1,252,500 = 7.984032E-07. (ISBN,BookId) 列的所有组合也是唯一的并且具有相同的密度.
最后的结果集称为直方图. 直方图中的每个记录 (称为直方图步骤) 都包括统计信息 (索引) 最左列中的样本键值以及有关从前一个值到当前 RANGE_HI_KEY 值的值范围内的数据分布的信息. 让我们更深入地检查直方图列.
RANGE_HI_KEY 列存储密钥的样本值. 此值是直方图步骤定义的范围的上限键值. 例如, 图 3-1 中直方图中的记录 (步骤)#3 与 RANGE_HI_KEY ='104-0100002488'存储有关从 ISBN>'101-0100001796'到 ISBN <='104-0100002488'的间隔的信息.
RANGE_HI_KEY 列存储密钥的样本值. 此值是直方图步骤定义的范围的上限键值. 例如, 图 3-1 中直方图中的记录 (步骤)#3 与 RANGE_HI_KEY ='104-0100002488'存储有关从 ISBN>'101-0100001796'到 ISBN <='104-0100002488'的间隔的信息.
RANGE_ROWS 列估计间隔内的行数. 在我们的例子中, 记录 (步骤)#3 定义的间隔有 8,191 行.
EQ_ROWS 表示有多少行的键值等于 RANGE_HI_KEY 上限值. 在我们的例子中, 只有一行 ISBN ='104-0100002488'.
DISTINCT_RANGE_ROWS 表示密钥在区间内有多少个不同的值. 在我们的示例中, 键的所有值都是唯一的, 因此 DISTINCT_RANGE_ROWS = RANGE_ROWS.
AVG_RANGE_ROWS 表示间隔中每个不同键值的平均行数. 在我们的例子中, 键的所有值都是唯一的, 因此 AVG_RANGE_ROWS = 1.
让我们在索引中插入一组重复的 ISBN 值, 代码如清单 3-1 所示.
清单 3-1. 将重复的 ISBN 值插入索引中.
- ;with Prefix(Prefix)
- as ( select Num from (values(104),(104),(104),(104),(104)) Num(Num) )
- ,Postfix(Postfix)
- as
- (
- select 100000001
- union all
- select Postfix + 1 from Postfix where Postfix <100002500
- )
- insert into dbo.Books(ISBN, Title)
- select
- convert(char(3), Prefix) + '-0' + convert(char(9),Postfix)
- ,'Title for ISBN' + convert(char(3), Prefix) + '-0' + convert(char(9),Postfix)
- from Prefix cross join Postfix
- option (maxrecursion 0);
- -- Updating the statistics
- update statistics dbo.BooksIDX_Books_ISBN with fullscan;
现在, 如果再次运行 DBCC SHOW_STATISTICS('dbo.Books',IDX_BOOKS_ISBN) 命令, 您将看到如图 3-2 所示的结果.
图 3-2.DBCC SHOW_STATISTICS 输出
具有前缀 104 的 ISBN 值现在具有重复项, 这会影响直方图. 还值得一提的是, 第二结果集中的密度信息也发生了变化. 具有重复值的 ISBN 的密度高于 (ISBN,BookId) 列的组合, 这仍然是唯一的.
让我们运行 SELECT BookId,Title FROM dbo.Books WHERE ISBN LIKE'114%'语句并检查执行计划, 如图 3-3 所示.
图 3-3. 查询的执行计划
大多数执行计划操作员都有两个重要的属性. 实际行数表示在执行者执行期间处理了多少行. 估计行数表示 SQL Server 在查询优化阶段为该运算符估计的行数. 在我们的例子中, SQL Server 估计有 2,625 行, 其中 ISBN 以 114 开头. 如果查看图 3-2 中所示的直方图, 您将看到步骤 10 存储有关 ISBN 间隔的数据分布的信息, 包括您正在选择的值. 即使使用线性近似, 您也可以估计接近 SQL Server 确定的行数.
关于统计数据, 有两件非常重要的事情需要记住.
1. 直方图仅存储有关最左侧统计 (索引) 列的数据分布的信息. 统计信息中有关于键值的多列密度的信息, 但就是这样. 直方图中的所有其他信息仅涉及最左侧统计列的数据分布.
2. 无论大小如何, SQL Server 在直方图中最多保留 200 个步骤
表和表是否已分区. 每个直方图步骤所覆盖的间隔随着表的增长而增加. 这导致在大表格的情况下不太准确的统计数据.
对于复合索引, 当索引中的所有列都用作所有查询中的谓词时, 将具有较低密度 / 较高百分比的唯一值的列定义为索引的最左列是有益的. 这将允许 SQL Server 更好地利用统计信息中的数据分布信息. 但是, 您应该考虑谓词的 SARGability. 例如, 如果所有查询都在 where 子句中使用 FirstName = @ FirstName 和 LastName = @ LastName 谓词, 则最好将 LastName 作为索引中最左侧的列. 尽管如此, 对于 FirstName = @ FirstName 和 LastName<> @ LastName 之类的谓词不是这种情况, 其中 LastName 不是 SARGable.
统计和执行计划
SQL Server 默认自动创建和更新统计信息. 数据库级别有两个控制此类行为的选项:
1."自动创建统计信息" 控制优化程序是否自动创建列级统计信息. 此选项不会影响始终创建的索引级统计信息. 默认情况下启用 "自动创建统计数据库" 选项.
2. 启用 "自动更新统计数据库" 选项后, SQL Server 会在每次编译或执行查询时检查统计信息是否过时, 并在需要时更新它们. 默认情况下也会启用 "自动更新统计数据库" 选项.
■提示可以使用 S TATISTICS_ NORECOMPUTE 索引选项控制索引级别上统计信息的自动更新行为. 默认情况下, 此选项设置为 OFF, 这意味着统计信息会自动更新. 在索引或表级别更改自动更新行为的另一种方法是使用 sp_autostats 系统存储过程.
SQL Server 根据影响统计信息列的 INSERT,UPDATE,DELETE 和 MERGE 语句执行的更改次数确定统计信息是否已过时. SQL Server 计算统计信息列的更改次数, 而不是更改的行数. 例如, 如果您将同一行更改 100 次, 则将其计为 100 次更改而不是 1 次更改.
有三种不同的方案, 称为统计信息更新阈值, 有时也称为统计信息重新编译阈值, 其中 SQL Server 将统计信息标记为过时.
1. 当表为空时, SQL Server 在向数据库添加数据时会过时
表.
2. 当表的行少于 500 行时, SQL Server 会在统计列每 500 次更改后过期统计信息.
3. 在 SQL Server 2016 和 SQL Server 2016 之前, 数据库兼容级别 < 130: 当一个表有 500 行或更多行时, SQL Server 会在每 500+(表中总行数的 20%) 变化后过期统计信息统计列.
在 SQL Server 2016 中, 数据库兼容级别为 130: 大型表上的统计信息更新阈值将变为动态, 并取决于表的大小. 表具有的行越多, 阈值越低. 在具有数百万甚至数十亿行的大型表上, 统计信息更新阈值可能只是表中总行数的一小部分. SQL Server 2008R2 SP1 及更高版本中的跟踪标志 T2371 也可以启用此行为.
表 3-1 总结了不同版本的 SQL Server 中的统计信息更新阈值行为.
表 3-1. 统计信息更新阈值和 SQL Server 版本
这导致我们得出一个非常重要的结论. 使用静态统计信息更新阈值, 触发统计信息更新所需的统计信息列的更改次数与表大小成比例. 表越大, 统计信息自动更新的次数就越少. 例如, 对于包含 10 亿行的表, 您需要对统计信息列执行大约 2 亿次更改, 以使统计信息过期. 建议尽可能使用动态更新阈值.
让我们来看看这种行为如何影响我们的系统和执行计划. 此时, 表 dbo. 书籍有 1,265,000 行. 让我们在表中添加 250,000 行, 前缀为 999, 如清单 3-5 所示. 在此示例中, 我使用的是未启用 T2371 的 SQL Server 2012. 如果在启用动态统计信息更新阈值的情况下运行它, 则可以看到不同的结果. 此外, SQL Server 2014 中引入的新基数估计器也可以改变行为. 我们将在本章后面讨论它.
清单 3-5. 将行添加到 dbo.Books
- ;with Postfix(Postfix)
- as
- (
- select 100000001
- union all
- select Postfix + 1
- from Postfix
- where Postfix < 100250000
- )
- insert into dbo.Books(ISBN, Title)
- select
- '999-0' + convert(char(9),Postfix)
- ,'Title for ISBN 999-0' + convert(char(9),Postfix)
- from Postfix
- option (maxrecursion 0);
- Now, let's run the SELECT * FROM dbo.Books WHERE ISBN LIKE'999%' query that selects all of the
rows with such a prefix.
如果检查查询的执行计划 (如图 3-7 所示), 您将看到非聚簇索引查找和键查找操作, 即使它们在您需要从表中选择近 20%的行时效率低下.
图 3-7. 查询的执行计划选择具有 999 前缀的行
您还将在图 3-7 中注意到, Index Seek 运算符的估计行数和实际行数之间存在巨大差异. SQL Server 估计表中只有 31.4 行, 前缀为 999, 即使有 250,000 行具有这样的前缀. 结果, 产生了非常低效的计划.
让我们通过运行 DBCC SHOW_STATISTICS('dbo.Books',IDX_ BOOKS_ISBN) 命令来查看 IDX_BOOKS_ISBN 统计信息. 输出如图 3-8 所示. 正如您所看到的, 即使我们在表中插入了 250,000 行, 统计信息也没有更新, 并且直方图中没有前缀 999 的数据. 第一个结果集中的行数对应于行中的行数. 上次统计信息更新期间的表. 它不包括刚刚插入的 250,000 行.
图 3-8.IDX_BOOKS_ISBN 统计数据
现在让我们使用 UPDATE STATISTICS dbo.BooksIDX_Books_ISBN WITH FULLSCAN 命令更新统计信息, 然后再次运行 SELECT * FROM dbo.Books WHERE ISBN LIKE'990%'查询. 查询的执行计划如图 3-9 所示. 估计的行数现在是正确的, 并且 SQL Server 最终得到了一个更有效的执行计划, 该计划使用聚簇索引扫描, I / O 读取比以前少了大约 17 倍.
图 3-9. 统计信息更新后查询选择具有 999 前缀的行的执行计划
如您所见, 不正确的基数估计可能导致执行计划效率极低. 过时的统计数据可能是不正确基数估计的最常见原因之一. 您可以通过检查执行计划中的估计和实际行数来查明其中一些情况. 这两个值之间的巨大差异通常表明统计数据不正确. 更新统计信息可以解决此问题并生成更高效的执行计划.
统计维护
正如我已经提到的, SQL Server 默认自动更新统计信息. 对于小表, 这种行为通常是可以接受的; 但是, 对于具有数百万或数十亿行的大型表, 您不应该依赖自动统计信息更新, 除非您使用的 SQL Server 2016 的数据库兼容级别为 130 或启用了跟踪标志 T2371. 按 20%统计信息更新阈值触发统计信息更新所需的更改次数将非常高, 因此, 不会经常触发更新.
在这种情况下, 建议您手动更新统计信息. 在选择最佳统计维护策略时, 您必须分析表的大小, 数据修改模式和系统可用性. 例如, 如果系统在工作时间之外没有负载, 您可以决定每晚更新关键表的统计信息. 不要忘记统计信息和 / 或索引维护会为 SQL Server 增加额外的负载. 您必须分析它如何影响同一服务器和 / 或磁盘阵列上的其他数据库.
设计统计维护策略时需要考虑的另一个重要因素是如何修改数据. 对于具有不断增加或减少的键值的索引, 您需要更频繁地更新统计信息, 例如当索引中最左侧的列定义为标识或填充序列对象时. 如您所见, 如果特定键值超出直方图, SQL Server 会大大低估行数. 在 SQL Server 2014 到 2016 中, 此行为可能会有所不同, 我们将在本章后面部分中看到.
您可以使用 UPDATE STATISTICS 命令更新统计信息. 当 SQL Server 更新统计信息时, 它会读取数据样本而不是扫描整个索引. 您可以使用 FULLSCAN 选项更改该行为, 该选项强制 SQL Server 读取和分析索引中的所有数据. 正如您可能猜到的那样, 该选项提供了最准确的结果, 尽管在大型表的情况下它可能会引入大量的 I / O 活动.
■注意重建索引时, SQL Server 会更新统计信息. 我们将在第 6 章 "索引碎片化" 中更详细地讨论索引维护.
您可以使用 sp_updatestats 系统存储过程更新数据库中的所有统计信息. 建议您使用此存储过程并在将其升级到新版本的 SQL Server 后更新数据库中的所有统计信息. 您应该与 DBCC UPDATEUSAGE 存储过程一起运行它, 它会更正目录视图中不正确的页面和行计数信息.
有一个 s ys.dm_db_stats_properties DMV, 它显示自上次统计信息更新以来对统计信息列所做的修改次数. 利用该 DMV 的代
来源: http://www.bubuko.com/infodetail-2890809.html