Hi Friends and Geeks,

I write this blog at a time when traffic in Hyderabad has come to a stand-still owing to incessant rains since morning today.

Coming to the topic of this blog-post, we know how important it is to identify unused indexes and rarely used non clustered indexes to reduce the IO foot print of an OLTP database. Primarily the index usage analysis is driven from the DMV “sys.dm_db_index_usage_stats”

Query typically used:

Steps for finding out the inefficient indexes are documented in detail at the following blog

Tuning Indexing Strategy – Simple Talk

Now, consider a scenario where your application is sending the read workloads to a readable secondary replica. In this case, index usage analysis also needs to consider the secondary replica’s dm_db_index_usage_stats

Have you wondered, how the records in dm_db_index_usage_stats DMV in a readable replica would look like

user_updates are always 0 for all indexes in replica

user_seeks, user_scans and user_lookups have values as per the index usage on the read workload on secondary

Running the query that was run on Primary would return no rows on secondary replica as user_updates are zero for all records. Hence to collect the stats the following query has to be used on secondary replica

Once the stats are collected, we need to combine them and take a call on the indexes that could be potentially dropped.

To simplify the process, I have created an excel spreadsheet (link below) that consolidates the stats from both primary and secondary and uses VLOOKUP magic to highlight the indexes with less than 5% Reads that could potentially be dropped


You can download this spreadsheet and replace the dummy data with data from your environment sorted by Index_Name for analyzing the index usage

Hope you find this reusable asset handy!!

Cheers! Veera

Like us on FaceBook |

Join the fastest growing SQL Server group on FaceBook

Follow me on LinkedIn