I got the following executionplan from a customer (IT IS AN ESTIMATED EXECUTIONPLAN)
and as you can see, then the sum of the percentages is more than 100%. That should be impossible, but when I started to look into it, the reason was quite obviously:
The shown executionplan shows the complete SQL statement, but only a part of it is executed, and the sum of the executed part is 100%. So lets look into the SQL statement (you must have the Adventureworks2008R2 installed)
from Person.Person)=1234select *from person.personsales.SalesOrderDetail
There are 19972 rows in the Person.Person table, so we will never execute the cross join between the begin and end. Therefore the 100% in the executionplan will only be calculated on the Select count(*) from Person.Person.
Now we know how much CPU+IO the executed parts takes (hover over the Cond With Query Cost operator). On my server with my databsae it will take 0.0787.
If I hover over the Nested Loops with a cost of 12866355% that wil NEVER be executed, then the operator cost is approx. 10127,94. Now the formula is 100*10127,94 /0.0787 which approximately equals 12866355%.
So that is the reason for the strange percentages: 100% equals the executed part and then we divide the operator cost for each “Non Executed” operators with the total cost for the executed parts.
What happens if the IF was true so that the cross join will be executed?
Well, then we get the same result.
If I execute the query then I get the following:
Strange. But basically I think I understand why the estimated executionplan acts as it does.
Imagine a SQL Statement like this:select *from person.personsales.SalesOrderDetailselect *from person.personsales.SalesOrderDetail
Now the Query got more complicated, but if the sum should get 100% no matter what, then it would also be wrong because we will never execute all code, so I like to conclude:
- The code that the Optimizer is 100% sure to execute will calculate 100%.
- All other path’s of the code will related to the “100% sure exetued 100%” so that you have an idea about how long time it takes.
Please challenge me on this.