问题描述
我有一个数据库，其中为所有表启用了自动统计信息，并且我也在sys.databases表中进行了验证.
I have a DB where auto stats are enabled for all the table and i have verified in sys.databases tables as well.
在这种情况下，任何超过修改的20％+ 500行的表都应触发自动收集统计信息，并应收集统计信息.但是在我的数据库中这没有发生.很少有表格修改百分比已经达到40％，但仍然没有统计数据 自动收集.是什么原因呢?
In this case any tables which goes above 20%+500 rows of modification should triggere the auto gather stats and stats should be gathered. But in my DB this is not happening. Few of the tables modification % already reached 40 % and still stats were not gathered automatically. What is the reason for that ?
我应该检查表的其他参数是否陈旧.
Should i check any other parameters for the tables which is stale.
到目前为止，我们唯一的选择是手动收集我们不希望做的统计信息.我们需要找到根本原因.请让我知道还有哪些其他因素会影响此?
As of now the only option we have is to manually gather stats which we dont want to do. we need to find the root cause. Please let me know what other factors would have influenced this ?
推荐答案
20％的阈值仅适用于大型表和旧的兼容性级别，如下文所述:
The 20% threshold applies only to large tables and to old compatibility levels as explained on the following article:
https://blogs.msdn.microsoft.com/ psssql/2016/10/04/默认自动更新统计阈值更改为sql-server-2016/
https://blogs.msdn.microsoft.com/psssql/2016/10/04/default-auto-statistics-update-threshold-change-for-sql-server-2016/
希望这会有所帮助.
Hope this helps.
问候
Regards,
阿尔贝托·莫里洛
SQLCoffee.com
Alberto Morillo
SQLCoffee.com
这篇关于收集统计数据上的异常行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！