Updating Statistics

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.

20150319-AlterIndexStatistics

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
FROM sys.sysindexes
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.

20150319-UpdateStats

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s