数据库参数调优--自动更新统计信息

原文： https://blog.csdn.net/z10843087/article/details/78356975

关于2371 trace：http://www.sqlservergeeks.com/sql-server-trace-flag-2371/

总结：

1.异步与同步更新（同步：业务会阻塞等待统计信息同步更新　　异步：当到达统计信息需要更新的阀值时，业务仍然使用旧的统计信息与查询计划，直到统计信息异步更新完成，然后业务才会重用新的统计信息）

2.开启 trace flag 2371 ：

DBCC TRACEON (2371,-1)

当开启这个flag时,统计信息的更新将会根据表数据量的总体大小动态调整触发更新条件

(如1000000数据 30000就会触发更新(3%),1000000000数据1000000就会触发(0.1%)),从而有可能提高系统整体性能.

背景

我们都知道SQL SERVER 借助统计信息来产生执行计划，而过时的统计信息会导致SQL SERVER选择错误的执行计划，导致严重的性能问题。在数据库中选项可以自动的更新统计信息。如下图：

默认他是开启的，所以我们很少去关注他或者修改他.

异步更新和同步更新

其实除了同步更新统计信息外，还有一个异步更新统计信息的选项。他和同步更新统计信息有什么关系呢？

同步更新

默认情况下，当自动更新统计信息设置为True时，当数据的更改（插入，更新，删除或合并）达到阈值时，估计的行数会过时，SQL Server查询优化器将自动更新统计信息。当统计数据陈旧时，执行计划可能会变得不理想，这可能导致性能下降。这个项确保您的统计信息尽可能保持最新。每次执行缓存的查询计划时，Optimizer将检查数据更改并根据情况生成新的统计信息。这个行为正是我们想要的，但有需要注意的是，当统计信息过期时，查询优化程序在编译和执行查询之前要等待更新的统计信息。知道这个更新动作完成才会继续执行查询。这会显着地减慢执行过程。

异步更新

即使现有统计信息过期，查询也会使用现有统计信息进行编译; 如果查询编译时的统计信息已过期，则查询优化器可以选择次优查询计划。在异步更新完成后，优化器使用更新的统计信息进行编译

从上面的对比可以知道，在需要进行自动更新是，异步更新可以不用等待更新统计信息更新完成，再去编译执行当前的查询。

如何查看选项

SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on
FROM sys.databases

使用ssms

如何修改

TSQL

ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO

注意：要开启异步更新需要首先开启自动更新统计信息选项。因为异步更新统计信息依赖于自动更新。

SSMS

在上图中的选项处直接修改就可以

适用场景

1.这个选项适用于OLTP的环境。因为OLTP的数据变动比较频繁，容易造成统计信息的更新。OLAP要慎重使用

2.应用程序经历了一个或多个查询等待更新的统计信息导致的客户端请求超时。在某些情况下，等待同步统计可能会导致应用程序出现大量超时，从而导致失败

3. 应用程序经常执行相同的查询，类似查询或类似的缓存查询计划。异步统计信息更新的查询响应时间可能比使用同步统计信息更新更为可预测，因为查询优化器可以执行传入查询，而无需等待最新的统计信息。这避免了延迟一些查询而不是其他查询。