I’m not the first person to write about cleaning up unused or redundant indexes. You can read many articles about how non-clustered indexes are expensive to store and maintain, that they can occupy valuable space in memory, and that they can negatively impact the performance of the write portion of any SQL Server workload. There are great scripts out there already – like sp_BlitzIndex – that can help identify index issues without any grunt work. So, nothing new in this area.

But reinforcement can be beneficial.

Plus, many other resources out there just talk about the quantitative aspects, like…

Here’s a DMV query to identify unused indexes!

…rather than the qualitative aspects, like…

Here’s why you might not want to delete this unused index.

They have queries that look at sys.dm_db_index_usage_stats and determine that, if reads are low and updates are high, the index is “wasteful.” Or they compare key columns in sys.index_columns and determine that two indexes are “the same” even if the key columns are in different order.

Like missing index recommendations, blindly following these absolutes can be disastrous, and I want to talk about how you should think a little harder before removing, changing, or consolidating indexes.

Low reads <> wasteful

It’s easy to look at usage stats and determine some index is “wasteful” because there are more writes than reads. Here are some other things to keep in mind when evaluating these numbers:

• Low usage might still be very important usage – think about queries run by the CEO, or only very infrequently as part of nightly, weekly, or monthly processes. A single seek instead of a scan against a wider index might be worth all of those updates, so determining context is important. It may be worthwhile to dig in and see which queries are making use of that index, even if rarely.
• Check seek and scan usage on both primary and all types of secondaries, including distributed availability groups. Indexes might have been created on the primary specifically for the read side of the workload. But don’t use a calculation comparing reads and writes on a secondary in isolation, since both user_ and system_ updates will be 0.

No recent usage <> not important

Sometimes, it will look like an index isn’t used for read operations at all. But lack of recent usage might not be representative of a full business cycle, especially if there has been a recent restart or configuration change. You might not have captured that important query by the CEO, periodic jobs, or other background processes.

An index might not even be represented in the DMV yet, so always make sure you check all indexes. Qquery sys.indexes and perform a LEFT OUTER JOIN against sys.dm_db_index_usage_stats. This is especially important when checking availability group secondaries, where workloads can have very different patterns.

Same key columns <> identical

Some of the queries I see just look for the same key columns without paying attention to order. But an index on CustomerID, OrderDate is not the same as an index on OrderDate, CustomerID, and they might not be redundant – even if one doesn’t have any recent read activity.

You might even have two indexes that have the exact same key columns, but different filters, INCLUDE columns, or both. For example, on Stack Overflow’s Posts table, you may want an index on WHERE PostTypeId = 1 (questions) and another identical index on WHERE PostTypeId = 2 (answers). For included columns, this is less common, but you might have a very wide covering index to satisfy big reporting queries, and another narrower index with only a couple of included columns for non-reporting use cases. Either of those might have a filter, too.

If you are relying on sp_helpindex, keep in mind that it has never been updated for newer features like filters and included columns. It might make you think two indexes are identical when they are, in fact, very different. Please use other resources like the aforementioned sp_BlitzIndex or Kimberly Tripp’s sp_SQLskills_helpindex.

Other cleanup considerations

Another aspect I consider part of cleanup is fixing any indexes that might not be created correctly. Even indexes that are being used by read queries can often be better optimized. Some indexes:

• have key columns out of order;
• are missing an include column;
• have columns that aren’t used;
• are not unique when they should be; or,
• are not filtered when they should be.

A common fix I’ve made is when indexes are unique by definition (e.g. they lead by the primary key), but aren’t created that way, I re-create them as unique. This doesn’t necessarily help anything performance-wise, but declaring them this way makes their content and purpose much clearer to other maintainers. This includes folks writing queries who might otherwise think they need to use DISTINCT to eliminate duplicates. Let the metadata be the documentation even if you have a very strict policy of, say, prefixing all unique indexes with UQ_.

Speaking of names, some indexes are created poorly – they were created one way and have since changed, or just as a test, and the old name remains. It can be tempting to just fix these quietly but, be careful; indexes might be explicitly hinted in queries, deployment scripts, or maintenance routines, and renaming them will cause problems.

Another common fix is when a query asks for sorting that the index doesn’t quite support. I’ve seen cases where an index was defined as (<foreign key>) INCLUDE (<date>, ...) – which works great for the join to the parent table, but not for ORDER BY <foreign key>, <date>. Especially if there are a lot of rows per foreign key value. An easy fix is to move the second sort column into the key, e.g. (<foreign key>, <date>) INCLUDE (...). The index doesn’t take any more space, but it eliminates the subsequent sort operation in all the queries that ask for that ordering.

Now what?

Okay, you’ve identified some indexes to add, remove, or change. Doing this right will require knowledge of your workload, often beyond what index usage stats will tell you. Its history only goes back so far, and it can’t possibly know about features or query changes that will be introduced tomorrow.

If you’re not already sure why an index exists, get that context from wherever you can to ensure you are making informed decisions. Most indexes aren’t created in a bubble in one person’s mind. Ask the team that most recently worked on the feature that involves the table, if that is easy to discern. Review source control and ticket systems for mentions of the index. Search e-mail, Slack, and whatever you used for collaboration before Slack (Microsoft Teams? AOL Instant Messenger? IRC?). You may even need to widen the search – if you are trying to figure why some index exists on the CustomerOrders table, you might search for "<CustomerOrders>" and "slow" or even a more generic "orders" and "slow" or "orders" and "index".

Most importantly, make sure you test any potential index changes on a development or test server first. I know that it can be hard to simulate a realistic production workload, but you should have some idea about the heavier queries on your system that might be impacted – in either direction – by any of the index changes you’re planning. “Try it and find out” is a strategy, for sure, but be better prepared if you can.