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:

dbo.Users clustered index

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:

Aggressive Indexes details

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:

Previous Post
A Surprising Simplification Limitation
Next Post
300 Blogs And Running

11 Comments. Leave new

  • ¿Furry?

    Reply
  • DUI = Deleting Under (the) Influence (of tequila)

    Don’t Drink and Delete, folks.

    Reply
  • I have two tables listed as “aggressive under-indexing” each has 1 or two indices and each has a clustered index. what it it indicating to say under-indexing?

    Reply
  • Um…I’ll sit over here, thanks. But, this is now my favorite article you have written. Really great stuff…thanks again, Brent.

    Reply
  • Parag Shivajirao Shedbale
    July 31, 2019 12:12 am

    You are a beautiful person, Brent.

    Reply
  • SP_BlitzIndex just brought back 1,971 rows. Thats a tomorrow type of job

    Reply
  • Hi Brent
    Thanks again for sp_BlitzIndex – really useful!
    I’m seeing Aggressive Under-Indexing on a couple of tables which only have the primary key index (no non-clustered indexes at all)
    However, that is really the only index needed because the tables are only ever queried on the primary key. I was considering creating another index on the primary key, INCLUDEing the “common columns” people are SELECTing, so these queries will be covered by the index and SQL wouldn’t have to read the actual table, but I vaguely remember something on your Index Fundamentals course about not doing that for some reason (can’t find it now though!).

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}