5.20的翻译（补档）

最新推荐文章于 2021-06-28 01:22:47 发布

冬时快晴

最新推荐文章于 2021-06-28 01:22:47 发布

阅读量132

点赞数 1

统计和执行计划

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

1。自动创建统计信息控制优化器是否自动创建列级统计信息。此选项不影响始终创建的索引级别统计信息。默认情况下，自动创建统计数据数据库选项处于启用状态。

2。启用“自动更新统计信息数据库”选项后，SQL Server会在每次编译或执行查询时检查统计信息是否过期，并根据需要进行更新。默认情况下，也会启用自动更新统计数据数据库选项。

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行或更多行时，SQL Server会在统计列的每500+次（表中行总数的20%）更改后超过统计。

在数据库兼容级别为130的SQL Server 2016中：大型表上的统计信息更新阈值将变为动态的，并取决于表的大小。表中的行越多，阈值越低。对于具有数百万甚至数十亿行的大型表，统计更新阈值可能只是表中行总数的一个百分比的一小部分。还可以使用SQL Server 2008R2 SP1及更高版本中的跟踪标志t2371启用此行为。

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

这使我们得出了一个非常重要的结论。对于静态统计信息更新阈值，触发统计信息更新所需的统计信息列更改数与表大小成比例。表越大，自动更新统计信息的频率就越低。例如，对于具有10亿行的表，您需要对统计信息列执行大约2亿次更改，以使统计信息过时。建议尽可能使用动态更新阈值。

让我们看看这种行为是如何影响我们的系统和执行计划的。此时，表dbo。书有126500行。让我们用前缀999向表中添加250000行，如清单3-5所示。在本例中，我使用的是未启用t2371的SQL Server 2012。如果在启用动态统计信息更新阈值的情况下运行它，则可以看到不同的结果。此外，SQL Server 2014中引入的新基数估计量也可以改变这种行为。我们将在本章后面讨论。

清单3-5。向dbo.books中添加行

;with Postfix(Postfix)

(

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中注意到，索引查找运算符的估计行数和实际行数之间存在巨大差异。SQL Server估计表中只有31.4行的前缀为999，即使有25万行的前缀为999。因此，产生了一个效率极低的计划。

让我们通过运行dbcc show_statistics（“dbo.books”，idx_books_isbn）命令来查看idx_books_isbn统计信息。输出如图3-8所示。正如您所看到的，即使我们在表中插入了250000行，统计数据也没有更新，并且前缀999的柱状图中也没有数据。第一个结果集中的行数与上次统计更新期间表中的行数相对应。它不包括刚刚插入的250000行。

现在让我们使用update statistics dbo.books idx_books_isbn with fullscan命令更新统计信息，然后再次运行select*from dbo.books where isbn like'999%'查询。查询的执行计划如图3-9所示。估计的行数现在是正确的，并且SQL Server最终得到了一个更高效的执行计划，该计划使用的聚集索引扫描的I/O读取次数比以前少17倍。

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

统计维护

正如我已经提到的，默认情况下，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活动。

您可以使用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不会自动删除列级统计信息。您应该手动删除冗余的列级统计数据对象。