第十二周翻译 Statistics

SQL Server统计介绍

SQL Server统计信息是一些系统对象，它们包含索引键值中的数据分布信息，有时还包含常规列值中的数据分布信息。可以对支持比较操作的任何数据类型(如>、<、=等)创建统计信息。

让我们检查来自dbo的IDX_BOOKS_ISBN索引统计信息。我们在前一章的清单2-15中创建了Books表。可以使用DBCC SHOW_STATISTICS ('dbo。书”,IDX_BOOKS_ISBN)命令。结果如图3-1所示。

图3 - 1。DBCC SHOW_STATISTICS输出

如您所见，DBCC SHOW_STATISTICS命令返回三个结果集。第一个包含关于统计信息的一般元数据信息，例如名称、更新日期、更新统计信息时索引中的行数，等等。第一个结果集中的Steps列表示直方图中的步骤/值的数量(稍后将详细介绍)。查询优化器不使用密度值，仅用于向后兼容。

第二个结果集称为密度向量，它包含统计数据(索引)中键值组合的密度信息。它是根据1 / number of distinct values公式计算的，它表示每个键值组合平均有多少行。即使IDX_Books_ISBN索引只定义了一个键列ISBN，它还包含一个聚集索引键作为索引行的一部分。我们的表有1,252,500个唯一ISBN值，ISBN列的密度为1.0 / 1,252,500 = 7.984032E-07。所有(ISBN, BookId)列的组合也是惟一的，并且具有相同的密度。

最后一个结果集称为直方图。直方图中的每条记录称为直方图步长，包括统计信息(索引)最左边列中的样本键值，以及从上一个值到当前RANGE_HI_KEY值范围内的数据分布信息。让我们更深入地研究直方图列。

前缀为104的ISBN值现在具有重复值，这将影响直方图。同样值得一提的是，第二个结果集中的密度信息也发生了变化。具有重复值的ISBN s的密度高于(ISBN, BookId)列的组合，这仍然是惟一的。让我们运行SELECT BookId，即dbo中的Title。其中ISBN为' 114% '语句的图书，并检查执行计划，如图3-3所示。

大多数执行计划操作符都具有两个重要属性。实际行数指示操作符执行期间处理了多少行。预估行数指示在查询优化阶段为该操作符预估的SQL Server行数。在我们的示例中，SQL Server估计有2625行ISBN s以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会自动创建此类统计信息。

让我们看一个例子，创建一个表并用清单3-2中所示的数据填充它。

第一个INSERT语句中指定的姓和名的每个组合都被插入表50次。此外，还有一行名为Victor，由第二条INSERT语句插入。

现在，假设您希望运行一个查询，该查询仅基于FirstName参数选择数据。对于IDX_Customers_LastName_FirstName索引，该谓词是不可SARGable的，因为LastName列(索引中最左边的列)上没有SARGable谓词。

SQL Server提供了关于如何执行查询的两个不同选项。第一个选项是执行a群集索引扫描。第二个选项是使用非聚集索引扫描，同时对FirstName值与参数匹配的非聚集索引的每一行进行键查找。非聚集索引的行大小比聚集索引的行大小小得多。

它使用更少的数据页，而且与聚集索引扫描相比，非聚集索引扫描的效率更高，因为它执行的I/O读取更少。与此同时，当表中有大量具有特定名称的行并且需要大量键查找时，使用非聚集索引扫描的计划的效率将低于聚集索引扫描。不幸的是，直方图为IDX_Customers_LastName_FirstName索引只存储LastName列的数据分布，而SQL Server不知道FirstName数据分布。

让我们运行清单3-3中所示的两个选择，并检查图3-4中的执行计划。

如您所见，SQL Server决定对第一个选择使用聚集索引扫描(返回700行)，对第二个选择使用非聚集索引扫描(返回一行)。

现在，让我们查询sys。查看并检查表的统计信息。代码如清单3-4所示。或者，您可以研究dbo的统计节点。客户表格在管理工作室。

前两行对应表中的聚集索引和非聚集索引。最后一个名称以_WA前缀开头，显示列级统计信息，这些统计信息是在SQL Server优化查询时自动创建的。值得注意的是，SQL Server不会在列级统计数据创建之后自动删除它们。
■提示：考虑重新命名自动创建的_WA统计数据，以简化数据库管理。

让我们使用DBCC SHOW_STATISTICS ('dbo。命令，_WA_Sys_00000002_276EDEB3)。如图3-6所示，它存储了关于FirstName列的数据分布的信息。因此，SQL Server可以估计姓氏的行数(我们将其用作参数)，并为每个参数值生成不同的执行计划。

您可以使用create statistics命令在一个列或多个列上手动创建统计信息。在多个列上创建的统计信息类似于在复合索引上创建的统计信息。它们包括关于多列密度的信息，尽管直方图只保留最左边列的数据分布信息。

与列级统计信息维护相关的开销很大，尽管它比索引的开销要小得多，索引需要在每次发生数据修改时进行更新。在某些情况下，当特定查询不经常运行时，可以选择创建列级统计信息，而不是索引。列级统计数据帮助Q uery优化器找到更好的执行计划，尽管由于涉及索引扫描，这些执行计划不是最优的。与此同时，统计数据不会在数据修改操作期间增加开销，并且可以帮助您避免索引维护。但是，这种方法只适用于很少执行的查询。您需要创建索引来优化经常运行的查询。

最后，在向表添加新索引时，不要忘记重新计算和删除冗余的列级统计信息。

统计数字及执行计划

默认情况下，SQL Server自动创建和更新统计信息。在数据库级有两个选项可以控制这种行为:

1. Auto Create Statistics控制优化器是否自动创建列级统计信息。此选项不影响始终创建的索引级统计信息。默认情况下启用自动创建统计数据库选项。

2. 当启用自动更新统计数据数据库选项时，SQL Server每次编译或执行查询时都会检查统计数据是否过期，并在需要时更新它们。默认情况下还启用自动更新统计数据库选项。

■提示：您可以使用STATISTICS_ NORECOMPUTE索引选项控制索引级别统计信息的自动更新行为。默认情况下，该选项被设置为OFF，这意味着统计信息将自动更新。更改索引级或表级的自动更新行为的另一种方法是使用sp_autostats系统存储过程。

SQL Server根据执行的更改数量确定统计信息是否已过期插入、更新、删除和合并影响统计信息列的语句。SQL Server计算更改统计列的次数，而不是更改行的数量。例如，如果您将同一行更改100次，它将被视为100次更改，而不是1次更改。

有三种不同的场景，称为统计信息更新阈值，有时也称为统计信息重新编译阈值，其中SQL Server将统计信息标记为过期。

1. 当表为空时，SQL Server将超过向表添加数据时的统计数据。

2. 当表的行数少于500时，SQL Server在统计数据列每更改500次之后就会超过统计数据。

3.在SQL Server 2016之前和数据库兼容性级别< 130的SQL Server 2016中:当一个表有500行或更多行时，每500 +(表中总行数的20%)更改统计列一次，SQL Server就会超过统计。

在数据库兼容性级别为130的SQL Server 2016中:大表上的统计更新阈值变为动态的，并且取决于表的大小。表的行数越多，阈值越低。对于具有数百万甚至数十亿行的大型表，统计信息更新阈值可能只是表中总行数百分比的一小部分。在SQL Server 2008R2 SP1及以上版本中，还可以使用跟踪标记T2371启用此行为。

表3-1总结了不同版本SQL Server的统计更新阈值行为。

这就引出了一个非常重要的结论。使用静态统计信息更新阈值，触发统计信息更新所需的统计信息列的更改数量与表的大小成正比。表越大，自动更新统计信息的频率越低。例如，对于一个有10亿行的表，您需要对统计信息列执行大约2亿次更改，以使统计信息过期。如果可能，建议使用动态更新阈值。

让我们看看这种行为如何影响我们的系统和执行计划。此时，表dbo。书有126.5万行。让我们用前缀999向表中添加250,000行，如清单3-5所示。在这个例子中，我使用的SQL Server 2012没有启用T2371。如果在启用动态统计更新阈值的情况下运行它，您可以看到不同的结果。此外，SQL Server 2014中引入的新的基数估计器也可以更改行为。我们将在本章后面讨论它。

现在，让我们从dbo运行SELECT *。其中ISBN为'999%'的图书查询，该查询选择具有此类前缀的所有行。

如果检查查询的执行计划，如图3-7所示，您将看到非聚集索引查找和键查找操作，即使在需要从表中选择近20%的行的情况下，这些操作效率很低。

您还将注意到，在图3-7中，Index Seek操作符的估计行数与实际行数之间存在巨大差异。SQL Server估计，表中只有31.4行带有前缀999，尽管有250,000行具有这样的前缀。结果，产生了一个非常低效的计划。

让我们通过运行DBCC SHOW_STATISTICS ('dbo)来查看IDX_BOOKS_ISBN统计信息。， IDX_ BOOKS_ISBN)命令。输出如图3-8所示。正如您所看到的，即使我们向表中插入了250,000行，统计数据也没有更新，而且前缀999的直方图中也没有数据。第一个结果集中的行数对应于上次统计信息更新期间表中的行数。它不包括刚刚插入的250000行。

现在让我们使用update statistics dbo更新统计信息。使用FULLSCAN命令Books IDX_Books_ISBN，然后从dbo运行SELECT *。书中ISBN喜欢'999%'查询再次。查询的执行计划如图3-9所示。现在估计的行数是正确的，SQL Server最终得到了一个更高效的执行计划，它使用集群索引扫描，I/O读取比以前少17倍。

如您所见，不正确的基数估计可能导致非常低效的执行计划。过时的统计数据可能是不正确基数估计最常见的原因之一。您可以通过检查执行计划中的估计行数和实际行数来确定其中的一些情况。这两个值之间的巨大差异通常表明统计数据是不正确的。更新统计数据可以解决这个问题，并生成更有效的执行计划。

数据维护

如前所述，SQL Server默认情况下自动更新统计信息。对于小表，这种行为通常是可以接受的;但是，对于具有数百万或数十亿行的大型表，您不应该依赖自动统计信息更新，除非您使用数据库兼容性级别为130的SQL Server 2016或启用了跟踪标志T2371。按照20%的统计信息更新阈值触发统计信息更新所需的更改数量将非常高，因此不会经常触发更新。

在这种情况下，建议您手动更新统计信息。在选择最佳统计维护策略时，必须分析表的大小、数据修改模式和系统可用性。例如，如果系统在营业时间之外没有大量负载，则可以决定每晚更新关键表的统计信息。不要忘记统计数据和/或索引维护为SQL Server增加了额外的负载。您必须分析它如何影响同一服务器和/或磁盘阵列上的其他数据库。

在设计统计数据维护策略时要考虑的另一个重要因素是如何修改数据。对于键值不断增加或减少的索引，您需要更频繁地更新统计信息，例如当索引中最左边的列被定义为identity或被序列对象填充时。如您所见，如果特定的键值位于直方图之外，SQL Server会大大低估行数。在SQL Server 2014到2016年期间，这种行为可能会有所不同，我们将在本章后面看到。

可以使用update statistics命令更新统计信息。当SQL Server更新统计信息时，它读取数据的样本而不是扫描整个索引。您可以使用FULLSCAN选项来更改这种行为，该选项强制SQL Server读取和分析来自索引的所有数据。正如您可能猜到的那样，该选项提供了最准确的结果，尽管在大型表的情况下，它可能会引入繁重的I/O活动。

可以使用sp_updatestats系统存储过程更新数据库中的所有统计信息。建议您使用此存储过程，并在将其升级到新版本的SQL Server之后更新数据库中的所有统计信息。您应该将其与DBCC UPDATEUSAGE存储过程一起运行，该存储过程纠正编目视图中不正确的页和行计数信息。

有一个系统。dm_db_stats_properties DMV，它显示自上次统计信息更新以来对统计信息列所做的修改数量。利用DMV的代码如清单3-9所示。

查询的结果如图3-11所示，表明自上次统计信息更新以来，对统计信息列进行了250,000次修改。您可以构建一个定期检查sys的统计数据维护例程。dm_db_stats_properties DMV，并使用较大的modification_counter值重新构建统计信息。

另一个与统计相关的数据库选项是自动异步更新统计信息。默认情况下，当SQL Server检测到统计信息过期时，它将暂停查询执行，同步更新统计信息，并在统计信息更新完成后生成一个新的执行计划。通过异步统计信息更新，SQL Server使用基于过时统计信息的旧执行计划执行查询，同时异步地更新后台统计信息。建议您保持同步统计信息更新，除非系统有非常短的查询超时，在这种情况下，同步统计信息更新可以超时查询。

最后，SQL Server不会在创建新索引时自动删除列级统计信息。您应该手动删除冗余的列级统计对象。