During some testing with SQL Server 2014’s new cardinality estimator, I noticed something fun: the new CE can give you different index recommendations than the old one.
I’m using the public Stack Overflow database export, and I’m running this Jon Skeet comparison query from Data.StackExchange.com. (Note that it has something a little tricky at the top – it’s using a local variable for the @UserId, which itself makes for a different execution plan. When literals are used in the query, the behavior is different, but that’s another story for another blog post.)
First, here are the two different execution plans, both of which do about 1mm logical reads:
It’s a really subtle difference in the plans – at first glance, just looks like the 2014 CE removed a single operator – but the big difference is in the number of estimated rows returned:
- Old CE estimated 82 rows returned
- New CE estimated 352,216 rows returned
In actuality, 166 rows get returned with this particular input variable – the new CE is just flat out making bad guesses on this data.
Here are the different index recommendations:
CREATE NONCLUSTERED INDEX NewCE_OwnerUserId_Includes
ON [dbo].[Posts] ([OwnerUserId])
CREATE NONCLUSTERED INDEX OldCE_OwnerUserId_PostTypeId_Includes
ON [dbo].[Posts] ([OwnerUserId],[PostTypeId])
And when I run sp_BlitzIndex® after doing a little load testing, both missing index recommendations show up in the DMVs:
But surely the new CE’s recommendation is better. We’ll create just the one it recommends, and the resulting execution plan does 57k logical reads. Both the new CE and the old CE produce an identical plan, albeit with wildly different row count estimates (old 83, new says 37,423, actual is 166):
HAHAHA, now the new CE agrees that it needs the index recommended by the old CE in the first place. So let’s remove the new CE’s recommendation, and only create the old CE’s recommended index. Both the old and new CE choose to use it:
And even better, the old CE’s recommendation results in only 175 logical reads.
So what’s the takeaway? If you’re relying on the execution plan’s missing index recommendations for fast performance tuning, you’re not going to get the best results – no matter which cardinality estimator you’re using. With 2014, the recommendations are different, not necessarily better.
The real keys are knowing how to do it yourself, and we teach those in the Advanced Querying and Indexing 5-day in-person class next month.