Persist And Aggregate Index Stats Across Server Restarts

(This script is part of my SQL Server Metrics Pack project that is now available on GitHub.  If you like the code below and find it useful, I encourage you to check out the full project.)

Maintaining indexes can be an important part of a DBA’s job.  Researching an index to determine how useful it is, or if it needs to be removed, is something I do regularly.  Thankfully, SQL Server does a great job of keeping up with an index’s usage information behind the scenes, and you can find it by querying the built in sys.dm_db_index_usage_stats DMV.  This DMV provides important information about your indexes, such as the number of seeks, scans, lookups, and updates the index has received.  With this information a DBA can determine whether an index is used heavily, or not at all.

But guess what happens to all of that index info when SQL Server is restarted?

Goodbye Index Stats!

That’s right, it gets cleared out.  Every time SQL Server is restarted the index usage stats get reset to zero.

But I don’t wanna lose my index usage stats!

Me either, that’s why I came up with the quick-and-dirty solution below.  It stores the index usage stats for all indexes in a given database to a table, which will allow you to keep these stats.  With each restart a new record is inserted for each index, allowing you to be able to aggregate these stats across restarts, while also being able to see the usage for the previously recorded periods.

Before we get started, I want to point out that there’s a bug in  SQL Server 2012 and early versions of 2014 where index stats may get wiped out after an index rebuild.  The below code does not account for that, so if you are affected by this bug you may not get accurate results. See Kendra Little’s Index Usage Stats Bug Fixed post for more info.

First, we need a table for storing our index stats:

Now, we need a way to populate our table.  I chose to use a stored procedure for this so that you can easily specify the database you want to store index stats for.  It uses a simple query that gathers stats from several DMVs, and also generates a DROP statement for each index.  (Why not, right?)  It will then do a MERGE with our new IndexStats table, basing the MERGE on the generated DROP statement and current SQL Server start date/time.  If there is a match, it updates the stats, if not it creates a new record.  I toyed with the idea of using a CHECKSUM or HASHBYTES to base the MERGE on, but so far this has worked pretty well so I’m not in a hurry to change it.  Also, this procedure only checks nonclustered indexes, and excludes clustered indexes, primary keys and unique indexes.  That’s just my personal preference, you should be able to easily change that by editing the filters in the query’s WHERE clause.

Once you have the procedure created, all you have to do is call it and pass in the database you want to track index stats for.

I recommend putting this procedure and the above table somewhere neutral, I have a “DBA” database on each server where I put things like this.  That way you can use it to track multiple databases and all of the information is stored in one place.

So What Do You Think?

This is still a work in progress, so I would be very interested in hearing if anyone uses this and what your experience with it is.  As with anything, I’m sure other people will run into situations that I haven’t encountered or designed for.  I’m also working on a view that will aggregate the data that is gathered, I just haven’t gotten it fine tuned yet.  Look for it in a future post.

 

(Visited 3,719 times, 1 visits today)

5 thoughts on “Persist And Aggregate Index Stats Across Server Restarts

  1. Pingback: Random Blogging Challenge Results - Grant Fritchey

  2. Pingback: SQL Scripts: How To Find Index Usage - SQL Nuggets

  3. Pingback: SQL Scripts: How To Find Missing Indexes - SQL Nuggets

  4. Tareq

    Thanks for this script, however when I set up the table and store procedure and execute I got the error message: procedure loadindexstats has no parameters and arguments were supplied

Comments are closed.