Defragging Indexes Without Losing Index Stats - Part 2

In my last post, I introduced the concept of synchronizing index maintenance and index usage statistics logging as an important part of a DBA's performance tuning routine. In this post I will start delving into the individual scripts involved.

UPDATE: The full script (see link at end of post) has been both updated and tested (a bit)
                  I've also augmented the script below to clean up the atblIndexUsageStats table.

As a recap, below are the main steps involved:

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

Log Index Usage Statistics

For me this is the crux of what this series of blog posts is all about. In order to properly maintain the indexes in your database(s) SQL Server's index usage statistics, stored in the sys.dm_db_index_usage_stats DMV,  are crucial. However, as mentioned in my last post, they are zeroed out whenever an index is rebuilt or its statistics are updated. So they need to be stored elsewhere for reliable retrieval regardless of index maintenance schedules.

I store them in a special table [Admin_DB].[dbo].[atblTableMaintenanceHistory] (see my last post for the scripts). In order to properly store the usage statistics I've written a MERGE statement which allows you to add tables and indexes to your databases without needing to manually maintain the admin table. So, for tables that have already been inserted in a previous run it updates the stats by adding to the existing numbers. But it will insert new rows for any new tables.

There's a little more to it than that though. Since not all the indexes will be maintained on each run of the procedure, I've added a CASE statement to the update part of the MERGE query. As the DMV statistics are cumulative, this ensures that the figures in the maintenance history table of any stats that haven't been reset in the last run will be replaced while those that have been reset will be added to. 

IMPORTANT NOTE: The MERGE statement is only compatible with SQL Server 2008+ 

As this is the unique selling point of my maintenance procedure, you could just add this to one of the other scripts I mentioned in the last post: Defragging Indexes Without losing Index Stats - Part 1

Otherwise, you can come back for my next post and read more about my how I'm building my entire procedure. If you'd like a preview of the stored procedure that defrags and updates stats for all the databases on a server, feel free to download it. CAVEAT: It has not been fully tested yet.