Quick query to show how to get when your statistics (column or index) statistics were last updated. If you wanted to track how often they were being updated, you could capture this to a table and run some reports off it.
SELECT OBJECT_NAME(object_id) AS TableName, Name, 'Index' AS ObjectType, STATS_DATE ( object_id , index_id ) as StatsCreatedDate FROM sys.indexes UNION SELECT OBJECT_NAME(object_id) AS TableName, Name, 'Column Statistic' AS ObjectType, STATS_DATE(object_id, stats_id) AS StatsCreatedDate FROM sys.stats ORDER BY StatsCreatedDate DESC
Incidentally, this is also helpful in identifying any recently created indexes you may have created and forgotten to save to run on your various prod\qa\uat boxes [not that I’ve ever done this…].
Unfortunately, there is no create date for indexes or stats in the sys.indexes or sys.stats table to know when a particular index was created. You have to be quick though, as statistics are updated automagically by sql server all day long, when [information gleaned from Statistics Used by the Query Optimizer in Microsoft SQL Server 2005]:
- The number of rows in the table goes from 0 to more than 0
- The number of rows when the stats were created was less 501 [500 or less] and the column modification counter [often referenced as colmodctr] of the leading column of the statistics object has changed by more than 500 rows since.
- The table had more than 500 rows when the leading column of the statistics object has changed by 500 + 20% of the number of rows in the table when the stats were created.
Unless, you have AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS off. (Don’t do it. Eventually, you’ll regret it.)