weird execution plan

Today I was on The Heap and was looking at a query plan for something I thought could be improved. However, it created something that shook my belief in the SQL Server query optimizer. Can I still trust if it cannot even count to 100%?

Characteristics of the table:

  • clustered on a non-identity column
  • 12 indexes, one of them being the date_entered column in question
  • 60,000 records
  • 26 columns of various types and length
  • PAGE compressed table

Has anyone seen this before and what causes the plan to look so skewed?

And below from SQL Sentry Plan Explorer

SQL Sentry Plan Explorer - image Top Operations

  • 1
    I'm gonna say "Bug"? – RBarryYoung Apr 17 '13 at 21:46

This bug has been known for years, and won't ever be fixed. I first reported it 6 years ago but it has existed far longer than that. These are just estimated costs and don't really have any bearing on the optimizer itself and whether you can trust it. It's simply the showplan output that has some questionable math.

Solution: use SentryOne Plan Explorer, which tends to get the math right (or simply stop looking at estimated costs and look at more meaningful metrics). If you open this plan in Plan Explorer, the percentages will add up correctly.

| improve this answer | |
  • 1
    Looking at Top Operations, doesn't SentryOne Plan Explorer (Se-Qu-SPEx)™ just take the %s and scale them to fit 100%? Kinda like cheating... even I could do that. – 孔夫子 Apr 17 '13 at 20:57
  • 3
    @RichardTheKiwi That's basically all the ShowPlan visualizer is doing anyway, it just gets it wrong sometimes. – RBarryYoung Apr 17 '13 at 21:49
  • @RichardTheKiwi then do that. :-) All we are doing is relaying the estimated cost % that the ShowPlan XML gives us, and fixing the % relative to the rest of the estimated costs in that statement's plan. We can't make those estimated costs more meaningful, we can only correct what SSMS should have shown you in the first place. Again I'll stress that instead of the estimated cost % values you look at more meaningful metrics, which we've gone out of our way to try to provide in a more straightforward and efficient way than SSMS ever will. – Aaron Bertrand Apr 17 '13 at 22:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.