Last week, I was working on a project to analyse indexes on a database that was part of an availability group. The main goal was to find unused indexes that could be removed, but I was also interested in gaining an overall understanding of how the system was indexed.
Unused indexes not only take up disk space, but they also add overhead to write operations and require maintenance which can add additional load on your system. We can also use this analysis to look for a high number of lookups which could indicate we need to adjust indexes slightly.
Click through to see how you can connect together index usage stats from the primary and secondary replicas of an availability group.