November 12, 2014 at 5:51 am
Has anyone ever looked at an Estimated Execution Plan where the cost is over 100%?
I've got someone's query who's costs for a Clustered Index Scan is 50251% and 22887% for another one. There's a Key Lookup at 183% and a Nested Loops Left Outer Join at 2385%. This is just looking way out of whack.
Has anyone else ever seen something like this? What does it mean?
November 12, 2014 at 6:16 am
Oh yeah, I've seen that before. It comes from several sources. It could be that you're dealing with table variables or multi-statement table valued user defined functions. Because they don't actually have statistics, the estimations can be pretty wildly out of wack. I can demo this live. It could be that your statistics are out of date. It can just be that there are a whole slew of different issues accumulating so the optimizer comes up with odd numbers in the estimates. Here's a blog post I wrote several years ago on the topic. The comments have links to other examples.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 12, 2014 at 6:43 am
Thank you for the link, Grant. There are indeed a lot of variables in this proc. So it sounds like you've hit the nail on the head.
I'll take a look at your post.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply