SQL Server Home
Sum of operator costs is more than 100% (in the execution plans)
as By Design
11/12/2010 2:47:39 AM
User(s) can reproduce this bug
For certain queries, the sum of "Estimated Operator Cost" (as a percent) is more than 100%.
SQL Server 2008 R2 - Express Edition
Windows Vista 64-bit
Operating System Language
Steps to Reproduce
Run the following query, with "Include Actual Execution Plan" turned on:
CREATE TABLE T1 (X INT)
CREATE TABLE T2 (X INT)
IF EXISTS (SELECT * FROM T1 WHERE X=1)
OR EXISTS (SELECT * FROM T2 WHERE X=1)
DROP TABLE T1,T2
The execution plan shows two table scans, each with a relative cost of 100% (giving a total of 200%)
The cost of each table scan should be at most 50%, so the sum of the costs of all operators should be exactly 100%.
to post a comment.
Please enter a comment.
on 11/17/2010 at 5:28 PM
Thanks for taking the time to file this observation and simple repro. The strange cost percentage values that you observed are an aritifact of the specific structure of the query plan that are a bit confusing but ultimately do make sense. They will not adversely affect the running of the query in any way.
The concatenation operator has two children - the table scans. However the server expects that it will not be necessary to execute the second table scan. This is because it expects to find a row from the first table scan which would satisfy the "exists" part of the query. Therefore it does not include the cost of the second table scan when computing the total cost of the subtree rooted at the concatenation. However it still estimates and reports the cost of the second table scan, which the management tool the computes as a percentage of the total query cost as if it will be executed.
In this case of course, it would be necessary to run the second table scan since the first is empty. However the engine operates on the conservative assumption that there will always be at least one row (which may only have been added after the query was compiled).
to post a workaround.
Please enter a workaround.
© 2014 Microsoft