自动更新统计数据未更新统计信息。

Question

我正在使用SQL SERVER 2012，我的数据库中的自动更新统计信息已经开启。

从下面的链接中我了解到，每当表行数发生变化时，自动更新统计信息将会触发，触发条件为SQRT(1000 * 表行数)。 https://blogs.msdn.microsoft.com/srgolla/2012/09/04/sql-server-statistics-explained/

我创建了一个包含1000条记录的表。

SELECT TOP 500 Row_number()OVER (ORDER BY (SELECT NULL)) rn,
                name
INTO   stst
FROM   sys.objects 

创建统计数据

CREATE STATISTICS rn  
    ON stst (rn)  

CREATE STATISTICS name  
    ON stst (name)  

检查已创建的统计数据

DBCC show_statistics('stst', rn)  -- Rows 500 

DBCC show_statistics('stst', name) -- Rows 500

根据公式来看

select SQRT(1000 * 500) -- 707.106781186548

所以，如果我在我的表中添加/修改了707.106781186548条记录，自动更新统计信息应该会触发。 再向我的表中添加1000条记录，这应该足够触发自动更新统计信息。

INSERT INTO stst(rn,name)
SELECT TOP 1000 Row_number()OVER (ORDER BY (SELECT NULL)) rn,
                a.name
FROM   sys.objects a 

解除“自动更新统计信息”功能。

Select * from stst

检查统计数据

DBCC show_statistics('stst', rn)  -- Rows 500 

DBCC show_statistics('stst', name) -- Rows 500

很不幸，仍然只有500行。 即使在我的表中插入了1000条记录，显然大于707.106781186548，在执行SELECT时为什么自动更新统计信息没有触发？我在这里漏掉了什么？

- Pரதீப்

---

你漏掉了几个要点。改进的计算仅在2016年之前的版本中使用，前提是你启用了Trace Flag 2371。当数据被修改时，统计信息不会立即更新，只有在查询需要使用它们时才会更新（假设它们已经达到了修改阈值）。 - Erik Darling

一个微不足道的查询（例如没有WHERE子句的SELECT *）不会触发统计信息更新。尝试运行一个实际上让引擎执行某些操作并且统计信息可能有用的查询（例如在主键列上进行等值或范围查询）。 - Aaron Bertrand

@sp_BlitzErik 感谢你提供关于Trace Flag的信息，我之前从来不知道这个。 - Pரதீப்

@AaronBertrand - 感觉好愚蠢.. 现在我应该做什么.. 我是否应该删除问题，以便我能将其标记为已回答 - Pரதீப்

不要感到愚蠢，我从回答你的问题中学到了东西。我同意@sp_BlitzErik和/或AaronBertrand应该发布他们的回答。 - SqlZim

我会让 @AaronBertrand 把他的回答发表出来。他可以用这些积分。 - Erik Darling

Answer 1

新的计算仅在启用跟踪标志2371时使用，除非在SQL Server 2016上设置了上下文数据库兼容性级别为130，这是默认行为。请参阅Microsoft KB 2754171： 在SQL Server中控制自动统计信息（AUTO_UPDATE_STATISTICS）行为 数据修改时不会更新统计信息。当基于成本的优化发现查询的有趣统计信息已过时时，将触发统计信息更新。 对于非常简单（“琐碎”）的查询，优化器不会进入基于成本的优化，其中一个明显的计划始终是最佳的。在这种情况下，不会发生统计信息更新。 请参阅Greg Low的Microsoft White Paper SQL Server 2012中的计划缓存和重新编译。

- Paul White