翻译3

第一节

SQL Server Statistics简介SQL Server统计信息是在索引键值中，有时在常规列值中包含关于数据分布的信息的系统对象。可以在支持比较操作的任何数据类型上创建统计信息，例如>、<、=等。让我们检查上一章中在清单2-15中创建的dbo.Books表中的IDX_BOOKS_ISBN索引统计信息。可以通过使用DBCC SHOW_STATISTICS('dbo.Books',IDX_BOOKS_ISBN)命令执行此操作。结果如图3-1所示。

DBCC 数据输出

如您所见，DBCC SHOW_STATISTICS命令返回三个结果集。第一个包含有关统计信息的一般元数据信息，如名称、更新日期、更新统计信息时索引中的行数等。第一结果集中的Steps列指示直方图中的步骤/值的数量（稍后详细介绍）。

密度值不由查询优化器使用，仅用于向后兼容目的。第二个结果集，称为密度向量，包含有关密度的信息，用于组合来自统计（索引）的关键值。它是根据1/多个不同值公式计算的，它指示平均每个键值组合有多少行。即使IDX_Books_ISBN索引只定义了一个键列ISBN，它也包括作为索引行的一部分的聚类索引键。我们的表有12500个唯一的ISBN值，ISBN列的密度为1.0/1252500=7.984032E-07。

（ISBN，BookId）列的所有组合也是唯一的，并且具有相同的密度。最后一个结果集称为直方图。直方图中的每条记录，称为直方图步骤，包括统计（索引）最左侧列中的示例键值以及关于从前一个值到当前RANGE_HI_KEY值范围内的数据分布的信息。

 让我们更深入地研究直方图列。RANGE_HI_KEY列存储密钥的样本值。此值是直方图步骤定义的范围的上限键值。例如，在图3-1的直方图中用RANGE_HI_KEY='104-0100002488'记录(步骤)#3存储有关从ISBN>'101-0100001796'到ISBN<='104-0100002488'的间隔的信息。

RANGE_ROWS列估计间隔内的行数。在我们的例子中，记录（步骤）#3定义的间隔有8191行。EQ_ROWS指示有多少行的键值等于RANGE_HI_KEY上限值。在我们的例子中，只有一行ISBN='104-0100002488'。

DISTINCT_RANGE_ROWS指示在该间隔内键的不同值有多少。在我们的例子中，键的所有值都是唯一的，所以DISTINCT_RANGE_ROWS=RANGE_ROWS。

图3-1。DBCC SHOW_STATISTICS输出

57 AVG_RANGE_ROWS表示间隔中每个不同键值的平均行数。在我们的例子中，键的所有值都是唯一的，所以AVG_RANGE_ROWS=1。让我们用清单3-1所示的代码将一组重复的ISBN值插入索引中。

清单3-1。将重复的ISBN值插入索引。

将前缀（前缀）作为（从（值（104）、（104）、（104）、（104）、（104））Num（Num）中选择Num），

Postfix(Postfix)as(选择100000001union全部从Postfix中选择Postfix+1，其中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.Books IDX_Books_ISBN with fullscan;

　　

现在，如果再次运行DBCC SHOW_STATISTICS(“dbo.Books”，IDX_BOOKS_ISBN)命令，您将看到图3-2中所示的结果。

带有前缀104的ISBN值现在有重复，这影响直方图。还值得一提的是，第二结果集中的密度信息也发生了变化。具有重复值的ISBN s的密度高于（ISBN，BookId）列的组合，这仍然是唯一的。让我们运行SELECT BookId，Title FROM dbo.Books WHERE ISBN LIKE'114%'语句并检查执行计划，如图3-3所示。

大多数执行计划操作符有两个重要的属性。实际行数指示在操作符执行期间处理了多少行。估计行数指示SQL Server在查询优化阶段为该操作符估计的行数。

在我们的例子中，SQL Server估计有2625行，ISBN从114开始。如果查看图3-2所示的直方图，您将看到，步骤10存储有关ISBN间隔的数据分布的信息，该ISBN间隔包括您正在选择的值。

 即使使用线性近似，也可以估计接近SQL Server所确定的行数。关于统计学，有两件事情需要牢记。1。直方图仅存储最左侧统计（索引）列的数据分布信息。统计学中有关于键值的多列密度的信息，但就是这样。直方图中的所有其他信息仅与最左边的统计列的数据分布有关。2。SQL Server最多保留直方图中的200个步骤，而不管表的大小以及表是否被分区。每个直方图步骤所覆盖的间隔随着表的增长而增加。

这导致对于大型表的统计信息不那么准确。在复合索引的情况下，当索引中的所有列都用作所有查询中的谓词时，将具有较低密度/较高唯一值百分比的列定义为索引的最左侧列是有益的。这将允许SQL Server更好地利用统计数据中的数据分布信息。但是，您应该考虑谓词的SARGa.。例如，如果所有查询都在where子句中使用FirstName=@FirstName和LastName=@LastName谓词，那么最好将LastName作为索引中最左边的列。然而，对于像FirstName=@FirstName和LastName<>@LastName这样的谓词，情况并非如此，其中LastName不可SARGable。

 

 

 

 

 

 

 

 

 

 

 

第二节

统计和执行计划

默认情况下，SQL Server自动创建和更新统计数据。在数据库级别上有两个选项可以控制这种行为：1.自动创建统计信息控制优化器是否自动创建列级统计信息。此选项不影响始终创建的索引级统计数据。默认情况下启用了自动创建统计数据库选项。2。当启用“自动更新统计数据库”选项时，SQL Server在每次编译或执行查询时检查统计数据是否过时，并在需要时对其进行更新。默认情况下，还启用了自动更新统计数据库选项。

■提示您可以通过使用STATISTICS_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个统计列更改之后更新统计信息。三。在SQL Server 2016之前以及在数据库兼容级别<130的SQL Server 2016中：当一个表有500行或更多行时，SQL Server在每500+（表中总行数的20%）统计列发生变化之后更新统计信息。63在具有数据库兼容级别=130的SQL Server 2016中：大型表的统计更新阈值变得动态，并且取决于表的大小。表中的行数越多，阈值就越低。

在具有数百万甚至数十亿行的大型表中，统计更新阈值可能只是表中总行数的一小部分。这个行为也可以在SQL Server 2008R2 SP1和上面的跟踪标志T2371中启用。表3-1总结了不同版本的SQL Server中的统计更新阈值行为。

Default heavier     默认行为；database compatibility数据库兼容性；

Static thresold       静态阈值； dynamic threshold 动态阈值

触发统计信息更新所需的统计信息列的更改与表大小成比例。表越大，自动更新统计信息的频率就越低。例如，对于具有10亿行的表，需要对统计列执行大约2亿次更改以使统计信息过时。如果可能，建议使用动态更新阈值。让我们看看这种行为如何影响我们的系统和执行计划。

此时，表dbo。书有1265000行。让我们用前缀999向表中添加250000行，如清单3-5所示。在这个示例中，我使用的SQL Server 2012没有启用T2371。如果启用了动态统计更新阈值，则可以看到不同的结果。此外，在SQL Server 2014中引入的新基数估计器也可以改变行为。我们将在本章后面讨论。

清单3-5。向dbo.Books中添加行；以Postfix(Postfix)作为后缀(选择100000001union，全部选择Postfix+1，Postfix<100250000)插入dbo。Books(ISBN，Title)选择“999-0”+.(char(9)，Postfix)，“Title for ISBN 999-0”+.(char(9)，Postfix)从Postfix选项(max.sion 0)中选择“999-0”； 现在，让我们运行SELECT*FROM dbo.Books WHERE ISBN LIKE'999%'查询，该查询选择具有此类前缀的所有行。如果检查查询的执行计划，如图3-7所示，您将看到非集群索引查找和键查找操作，尽管在需要从表中选择将近20%的行的情况下，这些操作效率很低。

actual number of rows 实际行数；estimated number of rows 估计行数

您还会注意到，在图3-7中，Index Seek操作符的估计行数和实际行数之间存在巨大的差异。

SQL Server估计表中只有31.4行具有前缀999，即使有250000行具有这样的前缀。结果，产生了效率极低的计划。让我们通过运行DBCC SHOW_STATISTICS('dbo.Books'，IDX_BOOKS_ISBN)命令来查看IDX_BOOKS_ISBN统计数据。输出如图3-8所示。如您所见，尽管我们向表中插入了250000行，但是统计信息没有更新，并且前缀999的直方图中没有数据。第一个结果集中的行数对应于上一次统计更新期间表中的行数。它不包括刚刚插入的250000行。

现在，让我们使用UPDATESTATISTICSBo.Books IDX_Books_ISBN WITH FULLSCAN命令更新统计数据，然后再次运行SELECT*FROM dbo.Books WHERE ISBN LIKE'999%'查询。查询的执行计划如图3-9所示。现在估计的行数是正确的，并且SQL Server最终得到一个更高效的执行计划，该执行计划使用集群索引扫描，其I/O读数比以前减少了约17倍。

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

第三节

统计维护

如前所述，SQL Server默认情况下自动更新统计数据。这种行为通常对于小表是可接受的；但是，对于具有数百万或数十亿行的大表，除非使用数据库兼容级别为130或启用了跟踪标志T2371的SQL Server 2016，否则不应该依赖自动统计更新。

触发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存储过程一起运行它，该存储过程纠正目录视图中不正确的页和行计数信息。有一个sys.dm_db_stats_properties DMV，它显示自上次统计更新以来对统计列所做的修改数量。利用DMV的代码如清单3-9所示。

清单3-9。

使用sys.dm_db_stats_properties select s.stats_id as [Stat ID], sc.name + '.' + t.name as [Table], s.name as [Statistics] ,p.last_

updated, p.rows, p.rows_sampled, p.modification_counter as [Mod Count] from sys.stats s join sys.tables t on s.object_id = t.object_id join sys.schemas sc on t.schema_id = sc.schema_id outer apply sys.dm_db_stats_properties(t.object_id,s.stats_id) p where sc.name = 'dbo' and t.name = 'Books';

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

另一个与统计相关的数据库选项是自动异步更新统计信息。默认情况下，当SQL Server检测到统计信息过期时，它将暂停查询执行，同步更新统计信息，并在统计信息更新完成后生成新的执行计划。通过异步统计信息更新，SQL Server使用旧的执行计划（基于过时的统计信息）执行查询，同时异步更新后台中的统计信息。

建议您保持同步统计更新，除非系统具有非常短的查询超时，在这种情况下，同步统计更新可以超时查询。最后，在创建新索引时，SQL Server不会自动删除列级统计信息。您应该手动删除冗余的列级统计对象。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

转载自《Pro SQL Server Internals, 2nd edition》（pdf已传至群文件）的CHAPTER 3 Statistics中的Introduction to SQL Server Statistics（P55~P58）、Statistics and Execution Plans（P62~P65）、Statistics Maintenance（P68~P69）共三小节。