sp_BlitzIndex analyzes the design & health of your clustered & nonclustered rowstore indexes and gives you suggestions on how to get higher performance without changing your queries.
If you run it with no parameters, you get advice on the indexes in the current database:
When I first got started working with SQL Server, I assumed it was flawless because it came in a sealed box from a ginormous corporation. I figured they'd tested every edge case, and that there was no way things were gonna go sideways. These days, even though I read CU KB articles and giggle about…
If you're a graduate of my free How to Think Like the SQL Server Engine course - and you'd better be, dear reader - then you're vaguely familiar with DBCC SHOW_STATISTICS. It's a command that shows you the contents of a statistics histogram. When I'm doing the first two parts of the D.E.A.T.H. Method - (D)eduplicating identical…
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…
When you're tuning queries
It's sort of a relief when the first time you get your hands on it, you get the plan and there's a missing index request. Even if it's not a super high-value one, something in there is crying for help. Where there's smoke, there's a bingo parlor.
But does adding missing indexes from requests always make things better?
There have been so many improvements lately to our First Responder Kit scripts that it was time to update our tutorial videos. Each of these is about ten minutes long and shows how to use 'em:
How to Use sp_Blitz for a Health Check
https://youtu.be/5QAL9itupMA
How to Use sp_BlitzCache to Find Slow Queries
https://youtu.be/EkLuXURMwso
How to Use sp_BlitzIndex to Check Your Indexes
https://youtu.be/8Wo5M7kYO20
Be honest, here You don't care about most indexing problems. Duplicates, Borderline Duplicates, Unused, Wide, Non-aligned Partitions... All you're here for are the Missing Indexes. Wham, bam, your query finishes in less than a second, ma'am. Take this quarter, go downtown and have a rat gnaw that thing off your face. And I get it.…
sp_BlitzIndex® just can't keep a secret. That's a feature, not a bug. I wrote sp_BlitzIndex® specifically so that it would rat out some things that SQL Server is doing behind the scenes to secretly help you out-- because it's really better if you know about it. What are secret columns? SQL Server needs a unique…
