Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

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

share|improve this question
1  
I'm gonna say "Bug"? –  RBarryYoung Apr 17 '13 at 21:46
add comment

marked as duplicate by Jon Seigel, Paul White, Max Vernon, Marian, ypercube Apr 18 '13 at 11:18

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer

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.

http://connect.microsoft.com/SQLServer/feedback/details/267530/ssms-execution-plan-sometimes-exceeds-100

http://connect.microsoft.com/SQLServer/feedback/details/621330/sum-of-operator-costs-is-more-than-100-in-the-execution-plans

http://connect.microsoft.com/SQLServer/feedback/details/436184/huge-operator-cost-in-estimated-execution-plan

http://connect.microsoft.com/SQLServer/feedback/details/370798/query-plan-iterator-cost-percentage-way-off

Solution: use SQL Sentry Plan Explorer*, where we tend 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, I guarantee the percentages will add up correctly.

Disclaimer: I work for SQL Sentry.

share|improve this answer
    
Looking at Top Operations, doesn't SQL Sentry 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
2  
+1 And just want to emphasise that the estimated cost display problems are purely an SSMS issue - the costs used internally by the optimizer during exploration are (generally) correct and so the plan selected is 'trustworthy'. –  Paul White Apr 19 '13 at 2:30
add comment

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