As part of my talk at the Nottingham SQL Server User group last night, I offered some tips on how to track SQL Server index usage over time.
sys.dm_db_index_usage_stats is a very handy table for seeing how many seeks/scans and updates your indexes get. But there are a few issues with. Firstly it’s a DMV, so it will be flushed on a number of events:
- SQL Server Instance Restart
- Database Detach
- Database Close
- SQL Server 2012/2014 – Alter Index rebuild
For a good thorough check normally a full months worth of stats are needed, this normally covers most of the business processes. I may event be useful to compare stats across months so you also capture events such as Financial Year end.
A better break down of when the scans and seeks happened can be very useful. It’s all well and good knowing your index was scanned 5000 times and Seeked across 15000 times, but what processes were doing that? Was it OLTP workload during 9-5 or was it the overnight processing? This could make a big difference to any planned Index changes.
So here’s a technique I use. It’s pretty simple and allows you to tune it to your own needs,