PART1 表和索引信息
第三章 统计
SQL Server统计信息介绍
SQL Server statistics是系统对象，它包含关于数据分布的信息，这些信息位于索引键值中，有时也位于常规列值中。可以在支持比较操作的任何数据类型上创建统计信息，例如>、<、=等。
让我们检查来自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值范围内的数据分布的信息。让我们更深入地研究直方图列。
RANGE_HI_KEY列存储键的示例值。该值是直方图步骤定义的范围的上界键值。例如，在图3-1的直方图中，RANGE_HI_KEY = '104-0100002488'的record (step) #3存储了ISBN > '101-0100001796'到ISBN <= '104-0100002488'之间的间隔信息。RANGE_ROWS列估计了区间内的行数。在我们的例子中，记录(步骤)#3定义的间隔有8191行。EQ_ROWS表示键值等于RANGE_HI_KEY上限值的行数。在我们的例子中，只有一行ISBN = '104-0100002488'。except_range_rows表示在间隔内键的不同值有多少。在我们的例子中，键的所有值都是唯一的，所以except_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 ')。你将再次看到图3-2所示的结果。
图3 - 2.DBCC SHOW_STATISTICS输出
前缀104的ISBN值现在具有重复值，这将影响直方图。值得一提的是，第二个结果集中的密度信息也发生了变化。具有重复值的ISBN的密度比组合(ISBN, BookId)列的密度要高，这仍然是唯一的。
让我们从dbo运行SELECT BookId, Title。书中ISBN喜欢114%的报表和检查执行计划，如图3-3所示。
图3 - 3.查询的执行计划
大多数执行计划操作符都具有两个重要属性。实际行数表示在操作符执行期间处理了多少行。预估行数表示在查询优化阶段为该操作符预估的行数。在我们的例子中，SQL Server估计有2625行，ISBN以114开头。如果你查看图3-2所示的直方图，你将看到步骤10存储关于ISBN区间的数据分布的信息，其中包括你所选择的值。即使使用线性近似，你也可以估计与SQL Server确定的行数接近的行数。
关于统计学有两件非常重要的事情需要记住。
1.直方图仅存储关于最左边统计信息(索引)列的数据分布信息。统计中有关于键值的多列密度的信息，但仅此而已。直方图中的所有其他信息只与最左边的statistics列的数据分布有关。
2.不管表的大小以及是否分区，SQL Server在直方图中最多保留200个步骤。每个直方图步骤所覆盖的间隔随着表的增长而增加。对于大型表，这会导致不准确的统计信息。
在复合索引的情况下，当索引中的所有列在所有查询中用作谓词时，将唯一值的密度更低/百分比更高的列定义为索引的最左列是有益的。这将允许SQL Server更好地利用来自统计数据的数据分布信息。但是，你应该考虑谓词的可伸缩性。例如，如果所有查询都使用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次之后就会超过统计数据。
3.在SQL Server 2016之前，在数据库兼容性级别< 130的SQL Server 2016中:当一个表有500或更多行时，每500 +(表中总行数的20%)更改统计数据列，SQL Server就会超过统计数据。在SQL Server 2016中，数据库兼容性级别= 130:大型表上的统计数据更新阈值变为动态的，并取决于表的大小。表的行数越多，阈值越低。对于数百万甚至数十亿行的大型表，统计信息更新阈值可能只是表中总行数的一个百分比。SQL Server 2008R2 SP1及以上版本中的跟踪标志T2371也可以启用此行为。
表3-1总结了SQL Server不同版本中的统计信息更新阈值行为。
表3 - 1.统计数据更新阈值和SQL Server版本
这使我们得出一个非常重要的结论。使用静态统计信息更新阈值，触发统计信息更新所需的统计信息列更改的数量与表大小成比例。表越大，自动更新统计信息的频率越低。例如，对于具有10亿行的表，你需要对统计数据列执行大约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所示，你将看到非集聚索引查找和键查找操作，尽管在需要从表中选择几乎20%的行时，这些操作是低效的。
图3 - 7.为选择带有999前缀的行进行查询的执行计划
你还会注意到，在图3-7中，索引Seek操作符的估计行数和实际行数之间存在巨大差异。SQL Server估计表中只有31.4行带有前缀999，尽管有250,000行带有前缀。结果，产生了一个非常低效的计划。
让我们通过运行DBCC SHOW_STATISTICS ('dbo ')来查看IDX_BOOKS_ISBN统计信息。命令。输出如图3-8所示。正如你所看到的，即使我们向表中插入了250,000行，统计信息也没有更新，并且前缀999的直方图中也没有数据。第一个结果集中的行数与上次统计更新期间表中的行数相对应。它不包括刚刚插入的250,000行。
图3 - 8.IDX_BOOKS_ISBN统计
现在让我们使用update statistics dbo更新统计信息。Books IDX_Books_ISBN和FULLSCAN命令，然后从dbo运行SELECT *。书号如“999%”再次查询。查询的执行计划如图3-9所示。现在估计的行数是正确的，SQL Server最终得到了一个更高效的执行计划，它使用聚集索引扫描，I/O读操作比以前少了大约17倍。
图3 - 9.统计信息更新后，为选择前缀为999的行进行查询的执行计划
正如你所看到的，不正确的基数估计可能导致非常低效的执行计划。过时的统计数据可能是错误基数估计最常见的原因之一。你可以通过检查执行计划中估计的和实际的行数来查明其中的一些情况。这两个值之间的巨大差异常常表明统计数据是不正确的。更新统计信息可以解决这个问题，并生成更有效的执行计划。