Defragging Indexes Without Losing Index Stats - Part 1

Introduction

As part of my regular performance tuning and maintenance schedule I rely heavily on queries that interrogate the DMV sys.dm_db_index_usage_stats. Based on this DMV I can judge whether indexes should be removed, modified or left as they are. See my previous blog post on SQL Server DMVs. I also believe in the regular defragging of indexes in a database (although I recently read a post from Brent Ozar challenging this idea, but if your servers don’t have the RAM required for such caching as his article suggests and/or you'll never get the budget to upgrade, you’ll need to keep reading). 

These two tasks can run, however, at cross-purposes. The above mentioned DMV stats are cumulative, which make them great for ascertaining how useful an index is. But you need to keep in mind that the stats are rest in the following scenarios:

•  At the creation of the index, obviously
• At SQL Server restart
• And, most relevantly to this discussion, at either
• Index REBUILD
• Index UPDATE STATISTICS

For more information on this see: Rebuilding Indexes vs. Updating Statistics by Benjamn Nevarez.

So, a DBA has had to be very mindful of when index and statistics maintenance tasks were being executed. The problem that can occur is that if an index is rebuilt the read/write stats in the DMV will be removed. So if you run the index related DMV queries that index may look like it is not being used. I aggressively remove unused indexes. But if my index cleanup and maintenance schedule is out of sync, or my defragging/stats updates run very frequently, I’d be removing potentially useful indexes.

Well then, what's one to do? For a long time I have preferred to use index defragging scripts that run frequently but only rebuild or reorganize indexes that surpass preset fragmentation thresholds as opposed to SQL Server's built in maintenance jobs. That way long, resource intensive and often unnecessary maintenance windows are avoided. Plus, tables that are intensively updated  get attention as needed and vice versa. But the problem with this strategy is it is more difficult to keep track of which indexes have been rebuilt when. It is easier to synchronize your index performance cleanup with your defragging if you have a weekly defragging and/or statistics update schedule. In that case you just need to evaluate the DMVs just before your index maintenance tasks run. 

There are quite a few people who have made index maintenance scripts public. Some good ones are:

1. http://ola.hallengren.com/ provides a very comprehensive db maintenance procedure
2. Michelle Ufford provides another good one

But what always seems to be missing is the logging of index usage stats before the defragging or the statistics update runs. Not to mention the synchronizing of index rebuilds and statistics updates. For a good explanation of this see a great post by Thomas LaRock. 

So, what are the steps involved? 

1. Log Index usage stats (see tables below)
2. Determine which indexes need defragging and generate scripts
3. Generate update statistics scripts based on step 2
4. Log defrag operation to defrag history table
5. Execute defrag and update stats scripts
6. Cleanup logging tables (optional)

In order to log both the index usage statistics and the defragging history I created two tables:

• atblIndexUsageStats

• atblTableMaintenanceHistory

As a side note, I separate my admin tables into an Admin_DB database. This keeps any production databases tidy and makes the admin procedures more portable in my opinion.

In my next post I will go into the scripts involved in logging the stats and performing the table maintenance.