SQL Server索引使用情况统计信息多久更新一次，由什么触发? [英] How often are SQL Server Index Usage Stats Updated and what triggers it?
问题描述
还有其他类似的问题，请不要混淆.
There are some other similar question to this but, please, do not confuse.
我知道有一个函数STATS_DATE()可以知道统计信息在哪里更新，这很好，但是我想知道是什么触发了此统计信息的更新或终止.
我知道也有一份报告.
I know there's a function STATS_DATE() to know where the stats where updated, which is fine, but what I want to know is what triggers an update of this stats, or a cut-off.
I know there's a report for this as well.
但是上周，我在某些服务器上看到了统计信息，它们为我提供了非常好的信息，其中包含该特定数据库中主表的4位数字.
But last week I saw the stats in certain server and they gave me very good information with amounts of 4 digits for the main tables in this particular database.
现在在同一台生产服务器中查找，并且STATS_UPDATE函数返回了它们在上周六进行的更新，但是该服务器已经启动了数周，没有重新启动，甚至没有重新启动服务.所以我知道我基本上是在星期一早上才开始查看这些统计信息.
Right now looking in the same production server and the STATS_UPDATE function returned they were updated last Saturday, but this server has been up for weeks without reboot not even service restart. So I know I'm looking the stats accumulated basically just this Monday morning.
因此，我想知道在哪里可以设置此设置，以便服务器在清除日志或它使用的任何存储空间之前一直累积索引使用情况统计信息.
So, I would like to know where can I set this settings so the server keeps accumulating the index usage stats until I clear the log or whatever storage it use.
推荐答案
SQL Server为表和索引维护各种统计信息".
There are various "stats" that SQL server maintains for Tables and indexes.
-
直方图统计信息. Thes是查询优化器使用的统计信息. STATS_DATE()返回这些更新的最后日期/时间.直方图统计信息自动更新的标准是500行+表格的20％.因此，具有100,000行的表，您必须先更新20,500行，然后才能重新计算这些行.您无法更改自动统计信息更新的阈值，但是，可以关闭自动统计信息更新和/或手动更新特定表和索引的统计信息.
Histogram statistics. Thes are the stats that the query optimizer uses. STATS_DATE() returns the last date/time these were updated. The criteria for automatic updating of histogram statistics is 500 rows + 20% of the table. So a table with 100,000 rows, you'd have to update 20,500 rows before triggering a recalculation of these. You can't change the threshold for automatic statistic updating, however, you can turn off automatic statistic updating and/or manually update statistics on particular tables and indexes.
使用情况统计信息:可在sys.dm_db_index_usage_stats中找到这些统计信息.索引使用情况统计信息跟踪诸如SELECT查询中的查找和扫描之类的事件.它们不会持久保存，并会在重新启动sql server时重置.如果重建基础索引"ALTER INDEX ... REBUILD"，但不使用"ALTER INDEX ... REORG"，则这些统计信息也会被重置
Usage statistics: These are found in sys.dm_db_index_usage_stats. Index usage statistics keep track of things like seeks and scans from SELECT queries. They are not persisted and get reset on restart of sql server. These statistics also get reset if the underlying index is rebuilt "ALTER INDEX ... REBUILD", but not with "ALTER INDEX ... REORG"
运行统计信息:可在sys.dm_db_index_operational_stats中找到这些统计信息.运营统计信息包括页面拆分，叶级插入和PAGEIOLATCH延迟.这些也是不持久的.
Operational Statistics: These are found in sys.dm_db_index_operational_stats. Operational statistics are things such as page splits, leaf level inserts and PAGEIOLATCH delay. These are also non persisted.
这篇关于SQL Server索引使用情况统计信息多久更新一次，由什么触发?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
