In a long running project the database is changing day by day. We create new tables, optimize our queries, and of course, make new indexes. Usually when I finish an optimization process I forget an important step:
I always forget to check how many indexes I have that I don’t use anymore.
Sql server stores index statisctics in a sys.dm_ view named SYS.DM_DB_INDEX_USAGE_STATS. As the other sys.dm_ views, sql server clears statistics on every restart, so we have to check after missing indexes only for our database instance had run for significant time and we had collected enough statistics to decide if we delete an index or not.
Now after we have understood the basic, let’s see the query for showing our index statistics:
As we can see on our column names, we access seek, scan, lookup and index update statistics counted since the last start of our sql server instance. Our result can contain null statistics. In that case we don’t have any statistics about the given index since the last restart.
This script contains one parameter (@table_name). If we uncomment the 4th line, we can filter for indexes only on a given table.