第十二周翻译：Introduction to SQL Server Statistics,Statistics and Execution Plans,Statistics Maintenance

原著信息：《Pro SQL Server Internals, 2nd edition》CHAPTER 3 Statistics中的Introduction to SQL Server Statistics一节（即P55~P58）、Statistics and Execution Plans（P62~P65）、Statistics Maintenance（P68~P69）共三小节
作者：Dmitri Korotkevitch

Introduction to SQL Server Statistics   SQL Server统计简介

SQLServer统计信息是系统对象，它在索引键值中以及有时在常规列值中包含有关数据分布的信息，可以在任何支持比较操作的数据类型上创建统计信息，例如>、<、=等等。
  让我们看看dbbo的idx_books_bisn索引统计信息。在上一章中，我们在清单2-15中创建了图书表。您可以使用dbccshow_statistics(“dbo.books”,idx_books_bisn)命令执行此操作。结果如图3-1所示。

 图3-1   DBCCshow_statistics输出

  如您所见，DBCCShow_STATISTICS命令返回三个结果集。第一个包含有关统计信息的一般元数据信息，例如统计信息更新时索引中的名称、更新日期、行数等。第一个结果集中的Steps列表示直方图中步骤/值的数量(稍后将详细介绍)。该密度值不被查询优化器使用，仅用于向后兼容性目的。
  第二个结果集称为密度向量，包含有关统计信息(索引)中键值组合的密度信息。它是根据不同值的 1/数值 公式计算的，它指示每个键值组合平均有多少行。即使IDX_Books_ISBN索引只定义了一个键列ISBN，它也包含一个聚集索引键作为索引行的一部分。我们的表格有1,252,500个独特的ISBN值，ISBN列的密度为1.0/1,252,500=7.984032E-07。（ISBN、BookID）列的所有组合也是唯一的，并具有相同的特性密度.
  最后一个结果集称为直方图。直方图中的每条记录，称为直方图步骤，都包括统计信息(索引)最左边列中的示例键值和从前面到当前RANGEHIKEY值的值。让我们更深入地检查直方图列。

• Range_HI_KEY列存储密钥的示例值。该值是由直方图步骤定义的范围的上限键数值。例如，在图3-1的直方图中记录(步骤)#3和范围_HI_KEY="104-0100002488"关于ISBN>‘101-0100001796’到ISBN<=‘104-0100002488’的间隔。

• “范围_行”列估计间隔内的行数。在我们的情况下，由记录(步骤)#3定义的间隔具有8,191行。

• EQ_ROWS表示有多少行的键值等于Range_HI_Key上限值。在我们的例子中，只有一行ISBN=‘104-0100002488’。

• DISTICATION_REARY_ROWS表示在间隔内有多少不同的键值。在我们的例子中，所有键的值都是唯一的，所以DEVICATION_Range_ROWS=Range_ROWS。

• AVG_Range_ROW表示间隔中每个不同键值的平均行数。在我们的例子中，所有键的值都是唯一的，所以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.Books IDX_Books_ISBN with fullscan;

  现在，如果再次运行DBCCShow_STATICS(‘dbo.Books’，IDX_Books_ISBN)命令，您将看到如图3-2所示的结果。

 图3-2   DBCC显示统计量输出

  前缀104的ISBN值现在有重复项，这会影响直方图。还值得一提的是，第二结果集中的密度信息也被改变。密度fo具有重复值的RISBN比(ISBN、BookID)列的组合要高，这仍然是唯一的。
  让我们来运行SELECTBookID，从dbbo.books中的标题，其中ISBN类似于“114%”StateMen检查执行计划，如图3-3所示。

 图3-3   查询的执行计划

  大多数执行计划操作符都有两个重要属性。实际行数表示在操作员执行期间处理了多少行。估计行数表示在查询优化阶段为该操作符估计的行数。在我们的示例中，SQLServer估计有2,625行ISBN以114开头。如果你在厕所k在图3-2所示的直方图中，您将看到步骤10为ISBN间隔存储有关数据分布的信息，其中包括您所选择的值。即使是林EAR近似，您可以估计行数接近SQLServer所确定的值。

  关于统计，有两件非常重要的事情要记住。

1. 直方图只存储最左边的统计信息(索引)列的数据分布信息。统计中有关于键值的多列密度的信息，但是是吗？直方图中的所有其他信息仅与最左边的统计数据列的数据分布有关。
2. SQL Server在直方图中最多保留200个步骤，而不考虑表的大小和表是否被分区。每个直方图步骤所涵盖的间隔随着表的增长而增加。这导致大型表格的统计数据不太准确。

  对于复合索引，当在所有查询中使用索引中的所有列作为谓词时，最好将密度较低/唯一值百分比较高的列定义为索引的最左边列。这将使SQLServer能够更好地利用统计信息中的数据分布信息。但是，您应该考虑谓词的SARGable。例如，如果所有查询都使用firstname=@firstName和LastName=@LastName谓词，在Where子句中，最好将LastName作为索引中最左边的列。尽管如此，Thi对于像FirstName=@firstName和LastName<>@LastName的谓词，不存在这样的情况，其中LastName不是sartable。

Statistics and Execution Plans  统计和执行计划

默认情况下，SQLServer会自动创建和更新统计信息。在数据库级别上有两个控制此类行为的选项：

1. 自动创建统计信息控制优化器是否自动创建列级统计信息。此选项不影响总是创建的索引级统计信息。自动Cr默认情况下启用eateStatistics数据库选项。
2. 启用自动更新统计数据库选项时，SQLServer将在每次编译或执行查询时检查统计信息是否过时，并在需要时进行更新。自动更新状态默认情况下，还启用了“特性数据库”选项。

---

■ 提示 可以使用STATISTISTICS_NORECOMPUTE索引选项在索引级别控制统计信息的自动更新行为。默认情况下，此选项设置为OFF，这意味着统计数据为aut。动态更新。另一种在索引或表级别更改自动更新行为的方法是使用sp_autostats系统存储过程。

---

  SQL Server根据影响统计信息列的INSERT、UPDATE、DELETE和Merge语句执行的更改数来确定统计信息是否过时。SQLServer统计统计列的更改次数，而不是更改的行的数量。例如，如果您更改同一行100次，则它将被计算为100次更改，而不是1次更改。
  有三种不同的方案，称为统计更新阈值，有时称为统计重新编译阈值，其中SQL Server将统计标记为过时。

1. 当表为空时，当您向表添加数据时，SQLServer将超过统计信息。
2. 当一个表具有小于500行的值时，SQLServer将在统计列每500个更改后输出统计信息。
3. **在SQLServer 2016之前和SQLServer 2016中，数据库兼容性级别<130：**当表有500或更多行时，SQLServer在统计信息列的每500次更改(占表中行总数的20%)之后，将超过统计信息。
   **在具有数据库兼容性级别=130的SQLServer 2016中：**大型表的统计信息更新阈值变得动态，取决于表的大小。表的行越多，阈值就越低。在具有数百万或甚至数十亿行的大表格上，统计更新阈值可以仅仅是表中的行总数的百分比的一部分。还可以在SQLServer 2008R2 SP1及更高版本中使用跟踪标志T 2371启用此行为。

  表3-1总结了不同版本的SQLServer中的统计数据更新阈值行为。

 表3-1   统计更新阈值和SQLServer版本

	Prior to SQL Server 2016 SQL Server 2016之前	SQL Server 2016 with Database Compatibility Level < 130数据库兼容级别<130的SQLServer 2016	SQL Server 2016 with Database Compatibility Level = 130具有数据库兼容性级别的SQLServer 2016=130
默认行为	静态(~20%)阈值	静态(~20%)阈值	动态阈值
T2371	SQLServer 2008R2 SP1及以上的动态阈值	动态阈值	动态阈值(忽略跟踪标志)

  这使我们得出一个非常重要的结论。使使用静态统计更新阈值，触发统计信息更新所需的统计信息列更改数量与表大小成正比。建议在可能时使用动态更新阈值。
  让我们看看这种行为如何影响我们的系统和执行计划。此时，表DBO。书有1，265，000行。让我们将250,000行添加到前缀999的表中，如下所示清单3-5.在本例中，我使用的是未启用T 2371的SQLServer 2012。如果在启用动态统计更新阈值的情况下运行它，则可以看到不同的结果。此外，SQLServer 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);

  现在，让我们运行SELECT*From dbo.Books，其中ISBN类似于‘999%’查询，它选择具有这样一个前缀的所有行。
  如果检查查询的执行计划(如图3-7所示)，您将看到非聚集索引查找和键查找操作，即使在需要从表中选择几乎20%的行的情况下，这些操作效率低下。

 图3-7   选择带有999前缀的行的查询的执行计划

  您还会在图3-7中注意到，索引查找操作符的估计行数与实际行数之间存在巨大差异。SQL Server估计表中只有31.4行前缀为999，尽管有250,000行具有前缀。因此，产生了一个效率很低的计划。
  让我们通过运行DBCCShow_STATICS(‘dbo.Books’，IDX_Books_ISBN)命令来查看IDX_Books_ISBN统计信息。输出如图3-8所示。正如您所看到的，即使我们将250,000行插入到表中，统计数据也没有更新，前缀999的直方图中也没有数据。第一个结果集中的行数与上次统计更新期间表中的行数相对应。它不包括刚刚插入的250,000行。

 图3-8   IDX_Books_ISBN统计

  现在让我们使用FULLSCAN命令的UPDATE STATISTICS dbo.Books_ISBN更新统计信息，然后运行SELECT*From dbo.Books，其中ISBN类似于‘999%’查询。查询的执行计划如图3-9所示。现在估计的行数是正确的，SQLServer最终使用了一个使用集群Inde的更高效的执行计划X扫描比以前少17倍的 I / O读取。

 图3-9   在统计信息更新后选择带有999前缀的查询的执行计划

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

Statistics Maintenance 统计维护

正如我已经提到的，SQLServer默认自动更新统计信息。对于小型表来说，这种行为通常是可以接受的；但是，对于具有数百万或数十亿行的大型表，除非您使用的是自动统计更新，否则不应该依赖于自动统计更新。SQLServer 2016，数据库兼容性级别为130，或启用跟踪标志T 2371。通过20%的统计数据更新阈值触发统计数据更新所需的更改数量将非常高，因此不会经常触发更新。
  建议您在该情况下手动更新统计信息。在选择最优统计维护策略时，必须分析表的大小、数据修改模式和系统可用性。例如，如果系统在工作时间之外没有很大的负载，则可以决定每晚更新关键表的统计信息。不要忘记统计信息和/或索引维护会增加SQL Server的额外负载。必须分析它如何影响同一服务器和/或磁盘阵列上的其他数据库。
  在设计统计维护策略时要考虑的另一个重要因素是如何修改数据。在具有不断增加或减少的键值的索引情况下，需要更频繁地更新统计信息，例如索引中最左边的列被定义为标识或填充了WITH。H序列对象。正如您所看到的，如果特定的键值在直方图之外，SQLServer会极大地低估行数。这种行为在SQLServer 2014到2016年之间可能有所不同，我们将在本章后面部分看到这一点。
  可以使用UPDATE STATISTICS命令更新统计信息。SQL Server更新统计信息时，它读取数据示例，而不是扫描整个索引。可以使用FULLSCAN选项更改此行为，该选项强制SQL Server从索引中读取和分析所有数据。正如您可能猜测的，该选项提供了最准确的结果，尽管它可以在大表的情况下引入大量I/O活动。

---

■ 注意 SQLServer在重新生成索引时更新统计信息。我们将在第6章“索引碎片”中更详细地讨论索引维护。

---

  可以使用sp_updatestats系统存储过程更新数据库中的所有统计信息。建议您使用此存储过程，并在将其升级到SQLServer的新版本后更新数据库中的所有统计信息。您应该与DBCCUPDATEUSAGE存储过程一起运行这个过程，该存储过程纠正目录视图中不正确的页面和行计数信息。
  有一个sys.dm_db_stats_properties DMV，它显示了自上次统计数据更新以来对统计信息列所做的修改数。使用DMV的代码如清单3-9所示。

 清单3-9   使用sys.dm_db_stats属性

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所示的查询结果表明，自上次统计数据更新以来，对统计信息列进行了250,000次修改。您可以构建一个统计维护例程，它定期检查sys.dm_db_stats_properties、DMV，并使用大型修改_计数器值重新构建统计信息。

 图3-11   Sys.dm_db_stats_properties输出

  另一个与统计相关的数据库选项是自动更新统计。默认情况下，当SQLServer检测到统计信息过时，它会暂停查询执行，同步更新统计信息，并在统计信息更新完成后生成新的执行计划。莱特。使用异步统计更新，SQLServer使用基于过时统计信息的旧执行计划执行查询，同时在后台异步更新统计信息。建议您保持同步统计更新，除非系统有一个非常短的查询超时，在这种情况下，同步统计更新可以超时查询。
  最后，SQLServer在创建新索引时不会自动删除列级统计信息。您应该手动删除冗余的列级统计对象。