Index Usage Analysis on databases with AlwaysON Secondary Readable Replica

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:

SELECT  OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,
        i.name AS [Index Name] ,
        i.index_id ,
        user_updates AS [Total Writes] ,
        user_seeks + user_scans + user_lookups AS [Total Reads] ,
        user_updates - ( user_seeks + user_scans + user_lookups )
            AS [Difference]
FROM    sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK )
            ON ddius.[object_id] = i.[object_id]
            AND i.index_id = ddius.index_id
WHERE   OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
        AND ddius.database_id = DB_ID()
        AND user_updates > ( user_seeks + user_scans + user_lookups )
        AND i.index_id > 1
ORDER BY [Difference] DESC ,
        [Total Writes] DESC ,
        [Total Reads] ASC ;

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

SELECT  OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,
        i.name AS [Index Name] ,
        i.index_id ,
        user_updates AS [Total Writes] ,
        user_seeks + user_scans + user_lookups AS [Total Reads] ,
        user_updates - ( user_seeks + user_scans + user_lookups )
            AS [Difference]
FROM    sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK )
            ON ddius.[object_id] = i.[object_id]
            AND i.index_id = ddius.index_id
WHERE   OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
        AND ddius.database_id = DB_ID()
        AND i.index_id > 1
ORDER BY [Difference] DESC ,
        [Total Writes] DESC ,
        [Total Reads] ASC ;

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

IndexUsageAnalysis_Blog

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