Capturing Index Usage Stats

Overview

Index usage stats are invaluable for evaluating performance of existing indexes. Every time an index is used by the query-engine, an internal table is updated reflecting that usage. So, every time a scan, seek, lookup, or update operation is completed, a counter is incremented for the affected index. Bravo, SQL Server 2008+! I can tell which indexes are being used, and which aren’t.

Problem is, they aren’t persisted by SQL Server across server reboots. With Windows Update ensuring your Windows servers get rebooted at least monthly for security patches, the stats captured by SQL Server are likely to be of limited utility. If you are uncertain which indexes can be dropped safely, you need a way to determine if any given index is being used. Unused indexes may be getting updated millions of times a day for no reason. It’s not enough to look at the sys.dm_db_index_usage_stats DMV for a couple of days, then drop all the “unused” indexes. It’s really important, for business-critical systems, to ensure you take a longer-term look at index usage first. This ensures you don’t remove an index that is only used rarely, but when it is used is imperative to performance.

Index usage stats can be helpful.

Do *you* want to be the one to explain to the CEO why that month-end process is suddenly taking three days instead of three hours?

Solution

I wrote a script that can be scheduled in SQL Server Agent to capture index usage stats. These stats are inserted into a permanent table allowing analysis across multiple months, and even years.

I created a database to hold the index usage stats:

The following script was scheduled using a SQL Server Agent job. The job is scheduled to run once every 6 hours. Existing rows are replaced every 6 hours unless the server was just rebooted, in which case a new row is created. This methodology creates the required history to track usage of each index:

I use the following query to get the list of index usage stats:

If you have SQL Server 2016+, I’d suggest looking into using SQL Server Query Store since it offers a lot more functionality. However, the script approach I show above is extremely light-weight, and creates only a tiny extra load on the live SQL Server instance. Query Store, not so much.

I hope you found this script useful! If you have any questions, please don’t hesitate to leave a comment below.

Check out our other posts on Performance, as well as our list of Tools for SQL Server.