Redundant indexes in SQL Server are a phenomenon that is more common than I’d like to admit. I see it in quite a lot of shops. Meaning, that there’s still a significant target audience for this blog post!
This article published by Brent Ozar is very informative about redundant/duplicate indexes, what they mean, why they’re bad, and what should be done with them.
Also, a few years ago, Guy Glantser published a post about dropping redundant indexes. It’s very useful for finding all redundant indexes within all tables in a specific database.
But what both of these articles are missing – is the ability to easily generate Drop/Disable commands for these redundant indexes.
TL;DR
Here’s a script that can detect, get details on, and drop every redundant index, including fully duplicate indexes, redundant indexes, and partially redundant indexes:
For more details, please click here to read the full article on Madeira Data Solution’s official blog.
The blog post discusses the common problem of redundant indexes in SQL Server and their negative impact on query performance.
It contains different use cases and examples of redundant indexes based on index properties such as key columns, include columns, and how to remediate them by dropping the redundant index without affecting query performance.