SQL Server统计信息简介
SQL Server统计信息是系统对象,其中包含有关索引键值中的数据分布的信息,有时还包含常规列值。 可以在支持比较操作的任何数据类型上创建统计信息,例如>,<,=等。
让我们从上一章清单2-15中创建的dbo.Books表中检查IDX_BOOKS_ISBN索引统计信息。 您可以使用DBCC SHOW_STATISTICS('dbo.Books',IDX_BOOKS_ISBN)命令来完成此操作。 结果如图3-1所示。
图3-1. DBCC SHOW_STATISTICS输出
如您所见,DBCC SHOW_STATISTICS命令返回三个结果集。第一个包含有关统计信息的常规元数据信息,例如名称,更新日期,更新统计信息时索引中的行数等。第一个结果集中的“步骤”列指示直方图中的步数/值的数量(稍后将详细介绍)。查询优化程序不使用“密度”值,仅出于向后兼容性目的而显示。
第二个结果集称为密度向量,它包含有关统计(索引)中键值组合的密度的信息。它是基于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列存储密钥的样本值。 此值是直方图步骤定义的范围的上 限键值。 例如,图3-1中直方图中的 记录(步骤)#3与RANGE_HI_KEY ='104- 0100002488'存储有关从ISBN>'101-0100001796'到ISBN <='104- 0100002488'的间隔的信息。
RANGE_ROWS列估计间隔内的行数。 在我们的例子中,记录(步骤)#3 定义的间隔有8,191行。
EQ_ROWS表示有多少行的键值等于RANGE_HI_KEY上限值。 在我们的例 子中,只有一行ISBN ='104- 0100002488'。
DISTINCT_RANGE_ROWS表示密钥在区间内有多少个不同的值。 在我们的 示例中,键的所有值都是唯一的,因 此DISTINCT_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的密度高于(ISBN,BookId)列的组合,这仍然是唯一的。
让我们运行SELECT BookId,Title FROM dbo.Books WHERE ISBN LIKE'114%'语句并检查执行计划,如图3-3所示。
图3-3. 查询的执行计划
大多数执行计划操作员都有两个重要的属性。 实际行数表示在执行者执行期间处理了多少行。 估计行数表示SQL Server在查询优化阶段为该运算符估计的行数。 在我们的例子中,SQL Server估计有2,625行,其中ISBN以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默认自动创建和更新统计信息。 数据库级别有两个控制此类行为的选项:
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和SQL Server 2016之前,数据库兼容级别<130:当一个 表有500行或更多行时,SQL Serve会 在每500+(表中总行数的20%)变化 后过期统计信息统计列。
在SQL Server 2016中,数据库兼容级别为130:大型表上的统计信息更新阈值 将变为动态,并取决于表的大小。表具 有的行越多,阈值越低。在具有数百万甚至数十亿行的大型表上,统计信息更新阈值可能只是表中总行数的一小部分。 SQL Server 2008R2 SP1及更高版本中的跟踪标志T2371也可以启用此行为。
表3-1总结了不同版本的SQL Server中的统计信息更新阈值行为。
表3-1. 统计信息更新阈值和SQL Server版本
这导致我们得出一个非常重要的结论。使用静态统计信息更新阈值,触发统计信息更新所需的统计信息列的更改次数与表大小成比例。表越大,统计信息自动更新的次数就越少。例如,对于包含10亿行的表,您需要对统计信息列执行大约2亿次更改,以使统计信息过期。建议尽可能使用动态更新阈值。
让我们来看看这种行为如何影响我们的系统和执行计划。此时,表dbo。书籍有1,265,000行。让我们在表中添加250,000行,前缀为999,如清单3-5所示。在此示例中,我使用的是未启用T2371的SQL Server 2012。如果在启用动态统计信息更新阈值的情况下运行它,则可以看到不同的结果。此外,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);
现在,让我们运行SELECT * FROM dbo.Books WHERE ISBN LIKE'999%'查询,选择具有这种前缀的所有行。
如果检查查询的执行计划(如图3-7所示),您将看到非聚簇索引查找和键查找操作,即使它们在您需要从表中选择近20%的行时效率低下。
图3-7. 查询的执行计划选择具有999前缀的行
您还将在图3-7中注意到,Index Seek运算符的估计行数和实际行数之间存在巨大差异。 SQL Server估计表中只有31.4行,前缀为999,即使有250,000行具有这样的前缀。 结果,产生了非常低效的计划。
让我们通过运行DBCC SHOW_STATISTICS('dbo.Books',IDX_ BOOKS_ISBN)命令来查看IDX_BOOKS_ISBN统计信息。 输出如图3-8所示。 正如您所看到的,即使我们在表中插入了250,000行,统计信息也没有更新,并且直方图中没有前缀999的数据。第一个结果集中的行数对应于行中的行数。 上次统计信息更新期间的表。 它不包括刚刚插入的250,000行。
图3-8. 我DX_BOOKS_ISBN的特点
现在让我们使用UPDATE STATISTICS dbo.Books IDX_Books_ISBN WITH FULLSCAN命令更新统计信息,然后再次运行SELECT * FROM dbo.Books WHERE ISBN LIKE'990%'查询。 查询的执行计划如图3-9所示。 估计的行数现在是正确的,并且SQL Server最终得到了一个更有效的执行计划,该计划使用聚簇索引扫描,I / O读取比以前少了大约17倍。
图3-9. 统计信息更新后查询选择具有999前缀的行的执行计划
如您所见,不正确的基数估计可能导致执行计划效率极低。 过时的统计数据可能是不正确基数估计的最常见原因之一。 您可以通过检查执行计划中的估计和实际行数来查明其中一些情况。 这两个值之间的巨大差异通常表明统计数据不正确。 更新统计信息可以解决此问题并生成更高效的执行计划。
统计维护
正如我已经提到的,SQL Server默认自动更新统计信息。对于小表,这种行为通常是可以接受的;但是,对于具有数百万或数十亿行的大型表,您不应该依赖自动统计信息更新,除非您使用的SQL Server 2016的数据库兼容级别为130或启用了跟踪标志T2371。按20%统计信息更新阈值触发统计信息更新所需的更改次数将非常高,因此,不会经常触发更新。
在这种情况下,建议您手动更新统计信息。在选择最佳统计维护策略时,您必须分析表的大小,数据修改模式和系统可用性。例如,如果系统在工作时间之外没有负载,您可以决定每晚更新关键表的统计信息。不要忘记统计信息和/或索引维护会为SQL Server增加额外的负载。您必须分析它如何影响同一服务器和/或磁盘阵列上的其他数据库。
设计统计维护策略时需要考虑的另一个重要因素是如何修改数据。对于具有不断增加或减少的键值的索引,您需要更频繁地更新统计信息,例如当索引中最左侧的列定义为标识或填充序列对象时。如您所见,如果特定键值超出直方图,SQL Server会大大低估行数。在SQL Server 2014到2016中,此行为可能会有所不同,我们将在本章后面部分中看到。
您可以使用UPDATE STATISTICS命令更新统计信息。当SQL Server更新统计信息时,它会读取数据样本而不是扫描整个索引。您可以使用FULLSCAN选项更改该行为,该选项强制SQL Server读取和分析索引中的所有数据。正如您可能猜到的那样,该选项提供了最准确的结果,尽管在大型表的情况下它可能会引入大量的I / O活动。
注意:重建索引时,SQL Server会更新统计信息。 我们将在第6章“索引碎片化”中更详细地讨论索引维护。
您可以使用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所示,表明自上次统计信息更新以来,对统计信息列进行了250,000次修改。 您可以构建统计信息维护例程,定期检查sys.dm_db_stats_properties DMV并使用大的modification_ counter值重建统计信息。
图3-11. Sys.dm_db_stats_properties输出
其他与统计相关的数据库选项是异步自动更新统计信息。 默认情况下,当SQL Server检测到统计信息已过时时,它会暂停查询执行,同步更新统计信息,并在统计信息更新完成后生成新的执行计划。 通过异步统计信息更新,SQL Server使用旧的执行计划执行查询,该计划基于过时的统计信息,同时异步更新后台统计信息。 除非系统的查询超时非常短,否则建议您保持同步统计信息更新,在这种情况下,同步统计信息更新可能会使查询超时。
最后,SQL Server不会在您创建新索引时自动删除列级统计信息。 您应该手动删除冗余的列级统计信息对象。