第四次翻译

SQL Server统计介绍

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

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

图3 - 1。DBCC SHOW_STATISTICS输出

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

第二个结果集称为密度向量，包含关于组合密度的信息统计数据(索引)中的键值。它是根据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列存储键的示例值。这个值是直方图步骤定义的范围的上界键值。例如,用RANGE_HI_KEY = '104-0100002488'

在直方图中记录(步骤)#3图3-1存储了ISBN > '101-0100001796'之间的间隔信息至ISBN <= '104-0100002488'。

RANGE_ROWS列估计区间内的行数。在我们的在这种情况下，记录(步骤)#3定义的间隔有8191行。EQ_ROWS指示有多少行具有与RANGE_HI_KEY相等的键值上限价值。在我们的示例中，只有一行ISBN = '104-0100002488'。

distinct t_range_rows表示键有多少个不同的值在区间内。在我们的例子中，所有键的值都是唯一的，所以distinct t_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.Books IDX_Books_ISBN with fullscan; 

现在，如果运行DBCC SHOW_STATISTICS ('dbo。再次命令Books'，IDX_BOOKS_ISBN)参见图3-2所示的结果。

图3 - 2。DBCC SHOW_STATISTICS输出

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

图3 - 3。查询的执行计划

大多数执行计划操作符都具有两个重要属性。实际行数指示操作符执行期间处理了多少行。估计行数表示在查询优化阶段为该操作符估计的SQL Server行数。在我们的在这种情况下，SQL Server估计有2625行ISBN s从114开始。如果你看柱状图如图3-2所示，您将看到步骤10存储了ISBN的数据分布信息interval，它包含您正在选择的值。即使用线性近似，你也可以估计接近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将进行检查如果统计数据每次编译或执行查询和更新时都已过期如果需要他们。还启用了自动更新统计数据库选项违约。

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

SQL Server根据执行的更改数量确定统计信息是否已过期插入、更新、删除和合并影响统计信息列的语句。SQL Server如何计算很多时候，更改的是统计数据列，而不是更改的行数。例如,如果你把同一行改变100次，它就会被算作100次改变而不是1次。有三种不同的场景，称为统计更新阈值，有时也称为统计信息重新编译阈值，其中SQL Server将统计信息标记为过期。

1.当表为空时，当您将数据添加到表。

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

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

在SQL Server 2016中，数据库兼容性级别= 130:统计更新大型表上的阈值是动态的，并且取决于表的大小。表的行数越多，阈值越低。在大桌子上数百万甚至数十亿行，统计数据更新阈值可以是a表中总行数百分比的百分比。这种行为还可以启用跟踪标志T2371在SQL Server 2008R2 SP1和以上。

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

表3 - 1 ?。统计数据更新阈值和SQL Server版本

这就引出了一个非常重要的结论。使用静态统计更新阈值时，该数字触发统计信息更新所需的统计信息列的更改与表的大小成正比。的表越大，自动更新统计信息的频率就越低。例如，在一个具有需要对统计列执行约2亿次更改才能执行统计过时了。如果可能，建议使用动态更新阈值。让我们看看这种行为如何影响我们的系统和执行计划。此时，表dbo。书有126.5万行。

让我们用前缀999向表中添加250,000行，如清单3-5所示。在在这个例子中，我使用的SQL Server 2012没有启用T2371。您可以看到不同的结果，如果您运行启用动态统计信息更新阈值。此外，引入了新的基数估计器在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);

现在，让我们从dbo运行SELECT *。其中ISBN如'999%'查询，选择所有具有这样前缀的行。

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

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

您还将注意到，在图3-7中，估计值与实际值之间存在巨大的差异索引查找操作符的行数。SQL Server估计只有31.4行带有前缀表中的999，即使有250000行具有这样的前缀。因此，一个非常低效的计划是生成的。

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

图3 - 8。IDX_BOOKS_ISBN统计

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

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

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

数据维护

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

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

在设计统计数据维护策略时要考虑的另一个重要因素是数据是怎样的修改。在索引增加或减少的情况下，需要更频繁地更新统计信息键值，例如索引中最左边的列定义为identity或填充序列对象。如您所见，SQL Server大大低估了特定键的行数值在直方图之外。在SQL Server 2014到2016年期间，这种行为可能会有所不同将在本章后面看到。可以使用update statistics命令更新统计信息。SQL Server更新时它读取数据的一个样本，而不是扫描整个索引。

你可以改变这种行为通过使用FULLSCAN选项，它强制SQL Server读取和分析来自索引的所有数据。作为您可能会猜到，该选项提供了最准确的结果，尽管它可能会引入大量的I/O活动大桌子的情况。

 

如前所述，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活动。■

注意SQL Server在重建索引时更新统计数据。我们将在第6章“索引碎片”中更详细地讨论索引维护。可以使用sp_updatestats系统存储过程更新数据库中的所有统计信息。建议您使用此存储过程，并在将其升级到新版本的SQL Server之后更新数据库中的所有统计信息。您应该将其与DBCC UPDATEUSAGE存储过程一起运行，该存储过程纠正编目视图中不正确的页和行计数信息。有一个系统。dm_db_stats_properties DMV，它显示自上次统计信息更新以来对统计信息列所做的修改数量。利用DMV的代码如清单3-9所示。

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

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

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