Query Plan Oddities: Two Identical Missing Indexes

As I’ve been building labs for my Mastering Query Tuning class, I’ve been spelunking through data.stackexchange.com. It’s where anybody can write queries against the Stack Overflow databases, and share them with friends.

For example, Daniel Vandersluis wrote a query to see how many edits he has:

When I run that on my unindexed copy of the Stack Overflow database, I get a missing index recommendation – no surprise there:

Execution plan – view on PasteThePlan

Zooming in a little, note the impact – SQL Server is kinda saying, “Wow, if I had this index, the query would be 49.9998% faster!”

Almost but not quite 50%

Hmm. That’s an unusual impact number.

Now if you’ve been index tuning for a while, you might know that SSMS only shows the first missing index recommendation in the plan – not all of them. Therefore, when you see a missing index, it’s prudent to view the plan’s XML to see if there might be more.

And in fact, in this plan, there are TWO missing indexes – kinda:

Two indexes, one plan

That’s two missing index recommendations, for the same index, in the same plan, each of which will make a 49.9998% improvement in our plan.

So why is that? Take a closer look at our original query and note that it has two nearly identical CTEs at the beginning:

Nearly identical – but different, one filtering for PostTypeId 1, the other for 2.

For each of those, when SQL Server optimized that part of the query, it thought, “Wow, an index on PostTypeId and LastEditorUserId sure would help here!” Therefore, it recommends two missing indexes – each of which will make the query 49.9998% faster.

That’s such a nifty example of how SQL Server breaks queries down into parts and analyzes each of them independently, even generating missing index requests independently.

Related Experiments

Erik mused – would turning on Forced Parameterization help? Here’s the plan with forced parameterization turned on, and no, that didn’t help either – we still get two identical missing index hints.

How about using the same PostTypeId in both CTEs, so it’s effectively the same query? Nope, that doesn’t help either.

Using the same variable, @PostTypeId in each CTE? Nope, same problem. In every case, SQL Server optimizes each CTE independently, generating a separate (identical) missing index for each one.

Just For Fun: Missing Index Doublemint Twins

Which led me to have a little fun – I modified both Daniel’s CTEs to have an additional join to the Comments table. Left is before, right is after with the new join:

Left is before, right is with the new join to Comments

Now, when I run the query, I get FOUR missing index requests – two identical ones on Posts, and two identical ones on Comments:

Two pairs of identical twins

But of course, when you look at the graphical plan, all you see is the first missing index request with a 39.5856% improvement – which isn’t right either:

39% my butt

And that’s why you can’t trust the impact numbers, let alone the graphical plans’ recommendations or even the field order.