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. You’re busy! You have a million other things to do. Take a number, Non-Unique Clustered Indexes. Maybe you’re not all that Unique anyway. Maybe you’re just another dude (or dudette) with a beard (stranger things have happened) and tattoos (maybe a beard tattoo), wearing a plaid shirt (probably also a tattoo), wandering around a major metropolitan area.

You’re in a Mode 3 state of mind

When you run sp_BlitzIndex, you have some Modal options. Mode 3 gives you missing index details. Just missing index details. No Scrolling through warnings about the other horrors you’ve visited upon your poor data. But where do they come from? I’m glad you asked! No, really. I am. Brent says I’m not allowed to sleep until I hit 1000 blog posts. It’s been over a year. Shadows have voices.

Index Matching

SQL is lazy. Horribly lazy. It takes that ANSI-spaghetti query you halfheartedly wrote while waiting for lunch to show up and turns it into results magic. Before it does that, it goes through a process called Simplification, where it rewrites your query a bit by removing contradictions (where col = ‘thhbbbpppttt’ and col = ‘GILBERRRRRRRRT’). Some other stuff goes on during this process, but let’s stick to index matching. Once it has the query you meant to write ready, El Optimizer will think to itself “I’d really like to find indexes like these…” and goes hunting for them. When it doesn’t find its index soulmate, it logs that in the missing index DMV.

But there are problems

Not, like, world-ending, life-deaffirming problems. Just stuff you should be aware of. It’s very query specific, this DMV. It’s not looking at your whole workload, the way you should be doing, like a responsible DBA and/or Developer. Query runs. Optimizer wants index. Request is logged. This index may almost match an index you already have, match another request from a similar query, and sometimes, they’re just a cry for help.

Why just a cry for help? Sometimes the request will only be estimated to improve the cost of the query by something like 50%. Or less. Way less. And much like swallowing a handful of Good & Plentys, these requests are just a sign that something is really horrible. So horrible that The Optimizer can’t figure out a very helpful index. If you throw functions on columns in joins or where clauses, SQL won’t even bother asking for a index, because what would it do with it? Whole lotta nothin, that’s what.

It will also sometimes ask for some insane indexes. Ever see those requests that are like one key column, and then include every column? That’s because someone is running SELECT *, or the equivalent in EF, because they’re lazy and didn’t just pick the columns they actually need. It will also ask for MAX columns as includes, because it doesn’t check those things first. It’s fine, it just becomes your job to look at these things first.

No missing indexes, I’m a genius

Not so fast, Slowpoke. Kendra has a couple posts at her new home here and here that talk about what clears out index DMVs, and fixes for some long-outstanding bugs coming along.

Thanks for reading!