How to Fix sp_BlitzIndex Aggressive Indexes Warnings

sp_BlitzIndex gives your indexes a psychiatrist-style evaluation. It’s a lot like a real psychiatrist: it’s just flagging behaviors, and there’s not necessarily anything wrong with being a hoarder, or a workaholic, or a furry. They’re just behavioral traits. Let your freak flag fly.

One of the warnings is “Aggressive Indexes,” which means sys.dm_db_operational_stats reports minutes of blocking happening at the row or page level on this index. That doesn’t mean this index is the problem, though – it just means it’s involved.

Say we’re working with the dbo.Users table from my free How to Think Like the Engine class. Let’s say we’ve only got the clustered index, aka the white pages:

And let’s say you’re constantly running this query at the same time other folks are trying to run their own queries:

There will be a lot of blocking on the clustered index of the dbo.Users table – because that’s the only index we have. sp_BlitzIndex may report “Aggressive Indexes”, and point to the clustered index as being involved.

However, that doesn’t mean you need to DROP that index – it means you probably need to add other indexes to support your queries, like perhaps an index on DisplayName.

On the flip side, what if you’ve got dozens of indexes – on every field of the table – and they all include the Reputation field? Every time we run our UPDATE query, we’re going to need to gather locks across all those indexes in order to update the included Reputation field. We may end up with Aggressive Indexes warnings, but here, it’s not because we need to add more. It’s because we need to prune down our indexes.

Fixing blocking problems is about finding the right balance of enough indexes to support our workload, but not so many indexes that they slow down our delete/update/insert (DUI) queries. That’s why I talk about my rule of 5 & 5: around 5 indexes per table, and around 5 fields (or less) per index. (To learn more about that balance, check out my 3-day Mastering Index Tuning course.)

To make that troubleshooting easier, as of this month’s release, sp_BlitzIndex’s “Aggressive Indexes” warning comes in a few variants:

• Aggressive Under-Indexing: reported when there’s a lot of blocking, and the table has 0-3 nonclustered indexes. You probably need to add the right indexes to support your DUI queries.
• Aggressive Over-Indexing: when there’s a lot of blocking on a table with 10+ indexes. Your DUI queries are probably getting held up by acquiring locks across many of these indexes to update ’em.
• Aggressive Indexes: we’re not judging quantity here because you’ve got 5-9 indexes. The table probably just has the wrong indexes, but remember, we’re not judgmental here. We like just the right amount of wrong. However, you’ve got the wrong amount of wrong here, and you probably need to ditch the indexes that aren’t getting used, and add better indexes to support your DUI queries. Start examining the reads vs writes columns.

Here’s an example from a demo in Stack Overflow:

Now, remember, it isn’t an absolute slam-dunk: you still have to examine the indexes to figure out the right solution. I’ve seen situations where even 2-3 indexes were too many (especially if they included all of the fields in the entire table), and I’ve seen situations where a few dozen indexes were completely okay. sp_BlitzIndex is just about helping you catch behavioral patterns. It’s up to you to decide just how much freakiness you’re okay with.

I’m okay with a lot. Come sit by me.

Next steps for learning:

• Mastering Index Tuning class – Tuning Indexes to Avoid Blocking module
• Mastering Query Tuning class – Avoiding Deadlocks module
• Mastering Server Tuning class – Solving Blocking with Readable Replicas and RCSI module