SQL Server Home
Katmai: [also 2005] Graphical plan operator costs exceed 100%
as Won't Fix
7/3/2007 11:36:41 AM
User(s) can reproduce this bug
Sometimes the query operator costs shown in a graphical query plan exceed 100%.
SQL Server 2008 June CTP
Windows XP SP2 Professional
Operating System Language
Steps to Reproduce
Generate the graphical plan for this batch:
declare @data table (
Declare @Code varchar(3)
Declare @Description varchar(15)
Set @Code = 'ABC'
Set @Description ='Merge'
if exists (
select * from @data as T
where T.Code = @Code
update @data set Description = @Description
insert @data values (@Code, @Description)
The Table Update/Update and Table Insert/Insert operators each show a cost of 304%.
No cost exceeds 100%, and the sum of costs is 100%.
to post a comment.
Please enter a comment.
on 12/2/2007 at 8:43 PM
The cost of the conditional statement itself is logically the cost of of executing the conditional -- it should be independent of whether the then\else branches are taken. Changing the cost to some combination of 'conditional', 'else', 'then' cost is just hacking around the problem. It's non-trivial to determine the 'else' and 'then' costs since they can be arbitrary sets of statements.
Anyone looking directly at the showplan xml isn't going to be confused (clearly labeled <cond>, <then>, <else> segements + their costs) -- Its the percentages in the graphical representation which are confusing. Ideally this graphical representaion would have some other graphic for conditionals\loops and not treat them like an operator tree (i.e., becuase a conditional statement has three children, doesn't mean all three children will be executed). This would make the graphical representaion easier to read (right now you can't tell which child is the then\else\conditional part) and would also make the odd precentages shown disappear.
Give what we have left to do in the release, we will not be fixing this issue.
on 9/6/2007 at 9:03 AM
We'll check out the repro steps and assign this to a developer. Thanks for the catch!
Buck Woody, SQL Server Program Manager
on 7/3/2007 at 11:44 AM
I filed a very similar problem in connect ID # 267530
to post a workaround.
Please enter a workaround.
© 2013 Microsoft