Executionplan with crazy percentages

I got the following executionplan from a customer (IT IS AN ESTIMATED EXECUTIONPLAN)

2013-12-09 00_40_56-10.5.1.51 - Remote Desktop Connection

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)

if (select count(*)
from Person.Person)=1234
begin
	select *
	from person.person
	cross join 
	sales.SalesOrderDetail
end

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:

 

2013-12-09 00_52_50-10.5.1.51 - Remote Desktop Connection

Strange. But basically I think I understand why the estimated executionplan acts as it does.

Imagine a SQL Statement like this:

if (select count(*) from Person.Person)=19972
begin
	select *
	from person.person
	cross join 
		sales.SalesOrderDetail
end
else
begin
	select *
	from person.person
	cross join 
		sales.SalesOrderDetail
end

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:

  1. The code that the Optimizer is 100% sure to execute will calculate 100%.
  2. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s