Now that IndexOptimize is taking care of my indexes, what about the statistics? Is relying on ALTER INDEX and Auto Update Statistics enought? The CSS SQL Server Engineers recently pointed out that some ALTER INDEX statements update some statistics. They’ve put this lovely table together.
The last run of IndexOptimize worked on 653 indexes, using REBUILD on 100 and REORGANIZATION on the remaining 553. Those 553 did not have their stats updated. So only 100 of the 371k indexes had statistics updated with the last maintenance. Is Auto Update Statistics taking care of the remaining? This query uses STATS_DATE and sys.sysindexes to find statistics that haven’t been updated in over a day, i.e. older than my last IndexOptimize job.
SELECT OBJECT_NAME(id),name,STATS_DATE(id, indid),rowmodctr
WHERE STATS_DATE(id, indid)0
AND id IN (SELECT object_id FROM sys.tables)
(15495 row(s) affected)
There is another DMV, sys.dm_db_stats_properties, that can be used here, but the instance I’m working on is not current enough, post 2008 R2 SP2. Boo.
Looking at the 15,495 results, 74% were last updated on Saturday, 5 days ago, by the last remaining task in the old maintenance job.
What then is happening with the other hundreds of thousands of indexes? I’ve modified the above query a bit to remove the day old criteria, but even with that it’s only looking at 17 thousand indexes. That’s because of the rowmodctr clause. rowmodctr counts the total number of inserted, deleted, or updated rows since the last time statistics were updated for the table. rowmodctr has some remarkable features, hence the list in the Remarks section. While 15 thousand indexes are in use and important, the vast majority of the 161 thousand tables and 371 thousand indexes in this database are not updated regulary or even being used at all. Hooray for efficient design! I knew some of this data going into this project, but these exercises has given be a much better understanding of the scale.