October 20, 2009 at 2:50 am
I have an Execution Plan which contains 9 queries, the following is a breakdown of their cost, relative to the batch:
Query 1 - 93%
Query 2 - 0%
Query 3 - 0%
Query 4 - 1%
Query 5 - 0%
Query 6 - 2%
Query 7 - 2%
Query 8 - 2%
Query 9 - 0%
That's fine so far.
However when I investigate Query 1, I discover that not all of its operators add up to 100%. The following is a breakdown of all operators that have cost against them:
MergeJoin (Union) - 97%
Sort - 1%
Clustered Index Seek - 1%
Sort 1%
Nested Loop 2%
Index Seek - 7%
Key Lookup - 36%
Sort - 1%
Nested Loop 2%
Index Seek - 7%
Key Lookup - 39%
This all adds up to a grand total of 191%
I was under the impression that each query within an execution plan should add up to 100%
Any ideas?
October 21, 2009 at 6:29 am
The cost values displayed in execution plans are estimates. Becaues of this, they won't always add up to 100% or they may add up to WAY over 100%. It's nothing to worry about, but because of this, you need to be very cautious about using these values as a basis of measurement for which query or which operation in the query, is performing better.
"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
October 21, 2009 at 7:50 am
Hi Grant,
Thanks for your response.
The query plan I refer to is the Actual Execution Plan and not the Estimated one.
Does the Actual plan still include estimates?
October 21, 2009 at 7:55 am
Yep. Those values in actual execution plans are also estimates. The values for rows, cpu, stuff like that in an actual plan are real values, but the operator costs are estimates.
"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
October 21, 2009 at 8:34 am
The only difference between an estimated and an actual execution plan is that the actual execution plan includes some runtime information. Actual row counts, actual number of executions and a couple others. Other than those, the plans are the same.
All of the costs are calculated by the query optimiser as it's generating the plan. They are not recalculated at execution time, hence they are still estimated costs, regardless of whether it's an actual or an estimated exec plan
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply