为Azure SQL数据库自动更新SQL服务器统计数据的异步配置

本文探讨了Azure SQL Database和On-premises SQL instance的自动更新SQL Server Statistics异步配置。

绪论

统计数据在查询优化器任务中发挥了重要作用，以建立一个具有成本效益和优化的查询执行计划。查询优化器使用SQL Server统计数据作为信息源来了解列值的数据分布。许多数据库专业人员把注意力放在索引上，以实现性能优化。然而，如果你有正确的索引和不恰当的统计，SQL Server将不能有效地返回输出。

• 注意：永远不要忽视统计在查询优化中的重要性

对于一个数据库专业人员来说，有多种统计学配置选项可以用来优化使用统计学。在继续阅读本文之前，你应该熟悉统计学知识。

在《坚持SQL Server统计数据自动更新的采样率》一文中，我们讨论了自动更新SQL Server统计数据的阈值。动态阈值取决于表的cardinality，即数据行的数量。

如果我们看一下Azure SQL数据库的属性和导航选项，它有一个配置--自动异步更新统计数据，处于禁用状态（默认）。这个选项类似于企业内部的SQL Server实例。

你也可以使用sys.databases查询来验证auto_update_stats_async配置，如下所示。

Select
CASE is_auto_update_stats_async_on
    WHEN 0 THEN 'Disabled'
    WHEN 1 THEN 'Enabled Auto Update Stats Async'
End AS AsyncStatsUpdate
from sys.databases
where name = 'AzureDemoDatabase'

环境细节

本文使用Microsoft SQL Azure (RTM) - 12.0.2000.8版本来执行查询。如果你想部署一个azure的SQL实例，请参考SQLShack上的SQL Azure类别作为参考点。你也可以利用一个企业内部的SQL Server实例来了解SQL Server的统计数据。

在这篇文章中，我们将探讨这个功能、它的用法和它的影响。

自动异步更新SQL Server的统计数据

默认情况下，每个SQL Server和Azure SQL数据库都启用了自动统计更新。我们可以使用sys.databases的is_auto_update_stats_on列值来验证该配置。

Select  
case is_auto_update_stats_on
    WHEN 0 THEN 'Disabled'
    WHEN 1 THEN 'Enabled Auto Update Stats'
End AS AutoStatsUpdate
from sys.databases
where name = 'AzureDemoDatabase'

由于频繁的数据操作（更新、插入、删除），统计数据在OLTP环境中会变得陈旧。它限制了SQL Server准备一个优化的查询执行计划，它可能准备一个高成本的计划，影响你的资源利用率和其他SQL查询。使用自动统计数据更新，查询优化器会自动更新统计数据。因此，DBA总是专注于保持统计资料的更新。

让我们来了解自动更新状态异步功能被禁用（默认）和启用时的查询优化器行为。

自动更新统计信息异步 - 禁用（默认值）

在默认的自动更新SQL Server统计信息异步属性下，查询优化器在编译查询之前更新统计信息。当统计信息被更新时，查询优化器会等待统计信息更新，然后再为新的值重新编译计划。这就是同步统计更新。

异步自动更新统计信息 - 已启用

如果我们启用异步自动SQL Server统计更新，查询优化器就不会等待查询优化器来更新统计。它先执行查询，之后再更新统计。由于这种行为，查询不会使用更新的统计数据。SQL Server使用一个单独的线程来更新统计信息。然而，更新后的任何新查询都会使用最新的更新的统计信息。

这个功能需要在单个数据库层面上启用。例如，下面的查询在Azure SQL数据库上启用Auto_Update_Statistics_Async。

ALTER DATABASE AzureDemoDatabase SET AUTO_UPDATE_STATISTICS_ASYNC ON

你可以使用下面的T-SQL脚本或SQL Server Management Studio中的数据库属性来验证它。

这个选项适用于为索引或单列查询谓词创建的统计；使用创建统计语句生成的统计。

• 注意：如果你已经禁用了Azure SQL数据库的Auto_Update_Statistics，你必须在使用自动更新统计数字异步功能之前启用它

我们应该启用异步SQL Server统计功能吗？

这个问题的众所周知的答案是--这取决于！!

如果我们启用异步自动统计更新配置，SQL Server运行查询时，会假设它有最新的统计资料。它可能会产生一个快速的执行计划；但是，由于过时的统计资料，它可能会产生不优化的查询计划。如果你的数据分布和数据库工作量对行数和分布的影响较小，这个功能可能会有帮助。

假设你执行一个影响到大量数据行的批量更新操作。SQL Server的同步自动统计确保查询优化器根据修改后的数据的更新统计工作。然而，如果你启用了异步统计更新，查询计划（使用旧的统计）可能会产生性能问题，因为它没有最近的批量更新信息。

同样地，异步统计可能会因为锁的阻塞而增加并发问题。正如前面所强调的，SQL Server使用后台请求进行异步统计更新。在这个过程中，它获得了统计元数据的模式修改锁。如果任何会话正在使用该锁，它就会阻止异步更新。另一方面，这个异步更新会话可以阻止需要元数据对象的模式稳定性（Sch-S）锁的不同会话。

你可以根据具体要求来使用异步统计更新功能。例如，你想提高一个广泛的OLTP数据库的查询响应时间，并且不希望SQL Server等待更新统计数据（同步更新）。你必须了解启用这个功能的后果。你不应该直接在生产数据库上启用它。如果你想使用异步统计更新，请在开发环境中对其进行彻底的测试，其工作负载与生产类似。

Azure SQL数据库对异步自动SQL服务器统计更新的行为

在Azure SQL数据库中，我们可以启用数据库范围的配置--Async_Stats_Update_Wait_At_Low_Priority，以避免异步统计更新的并发性问题。

你可以执行以下脚本来检查数据库范围配置的默认值 -Async_Stats_Update_Wait_At_Low_Priority

SELECT [Name], value FROM 
sys.database_scoped_configurations
WHERE name='ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY'

值0表示默认情况下，azure SQL数据库不使用Async_Stats_Update_Wait_At_Low_Priority功能。

要启用它，请使用ALTER DATABASE SCOPED CONFIGURATION语句，如下所示。

ALTER DATABASE SCOPED CONFIGURATION SET
ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = ON
Go
SELECT [Name], value FROM 
sys.database_scoped_configurations
WHERE name='ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY'

使用这个选项，Azure SQL Server会等待后台请求，以获得模式修改锁。它使用低优先级队列坚持更新的统计数据。它允许请求用当前的统计数据来编译查询。假设后台请求无法获得锁。它得到一个超时，导致异步统计任务流产。在这种情况下，需要手动更新统计数据，或者它需要等待另一个更新统计数据的触发。

• 注意：

  1. 这个数据库范围的配置只适用于Azure SQL数据库和Azure SQL托管实例。如果你打算将其用于企业内部的SQL服务器，不用担心。它计划在即将发布的SQL Server中出现

  2. SQL Server总是对本地临时表使用同步统计更新。由于AUTO_UPDATE_STATISTICS_ASYNC配置，它没有任何影响。

  3. SQL Server全局临时表的统计工作取决于用户数据库中的AUTO_UPDATE_STATISTICS_ASYNC值。

总结

在这篇文章中，我们探讨了SQL Server统计配置，以实现统计信息的异步自动更新。异步统计的默认配置是禁用的。因此，每个环境的要求都是不同的；因此，我们不能对异步统计更新有一般性的指导。然而，在大多数OLTP环境中，它是不需要的。

除非你有特殊要求，否则你一定不要启用它。始终在较低的环境中测试配置，了解影响，并在生产数据库中进行规划。