I have just run an estimated execution plan and there is one step that is showing an estimated cost of 92029%!
The procedure is intended to delete data from a table based on the values in one date column - if its over 3months old then delete it. The whole query cost is 100% but the clustered index delete is 92029%. In the XML version the %cost is not findable .. :-/
SQL 2005 Std edition 64 bit sp3
Just remember, the estimated costs are just that, estimates. It's not common to see the numbers add up to something other than 100, but it's not uncommon either. It's the main reason why you can't use the estimated costs as the only measure when you're tuning a query. You must refer to the scans, the reads, the execution time, other stuff, as well as the cost estimates in the execution plan.
That's so not surprising if it is a bug (which it looks like it is).
Have you ever tried to work out the costs based on the raw data? That is something that majorly busted my balls when implementing plan viewing for SQL Everywhere.
Heh... I've found the actual costs are also "estimated". Grant will tell you that I have code that proves that even the actual execution plan can be 100% wrong. In fact, here it is...
/****************************************************************************************
Purpose:
This code demonstrates that the estimated and actual execution plans in SQL Server can
be 100% INCORRECT and that the execution plan should only be relied on to provide hints
as to what may be wrong with a query rather than an absolute indication. This code runs
in SQL Server 2005 only.
The code creates a temp table for 10 years worth of dates starting with 2000-01-01 using
two different methods. The first method uses a recursive CTE and the second method uses
a "Tally" table. The output of each method is directed to a "throw-away" variable to
take display delays out of the picture.
Please check both the actual and estimated execution plans and compare the % of batch.
Please see the following article on how to build a Tally table and how they can be used
to replace certain While Loops.
http://www.sqlservercentral.com/articles/T-SQL/62867/
****************************************************************************************/
SET NOCOUNT ON
--=======================================================================================
-- Recursive method shown by (Name with-held)
--=======================================================================================
PRINT '========== Recursive method =========='
--===== Turn on some performance counters ===============================================
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @BitBucket DATETIME --Holds display output so display times aren't measured.
--===== Execute the code being tested ===================================================
DECLARE @DateVal DATETIME
SET @DateVal = '2000-01-01'
;with mycte as
(
select @DateVal AS DateVal
union all
select DateVal + 1
from mycte
where DateVal + 1 < DATEADD(yy, 10, @DateVal)
)
select @BitBucket = d.dateval
from mycte d
OPTION (MAXRECURSION 0)
--===== Turn off the performance counters and print a separator =========================
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',90)
GO
--=======================================================================================
-- Tally table method by Jeff Moden
--=======================================================================================
PRINT '========== Tally table method =========='
--===== Turn on some performance counters ===============================================
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @BitBucket DATETIME --Holds display output so display times aren't measured.
--===== Execute the code being tested ===================================================
DECLARE @StartDate AS DATETIME
SET @StartDate = '2000-01-01'
SELECT TOP (DATEDIFF(dd,@StartDate,DATEADD(yy,10,@StartDate)))
@BitBucket = @StartDate-1+t.N
FROM dbo.Tally t
ORDER BY N
--===== Turn off the performance counters and print a separator =========================
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT REPLICATE('=',90)
GO
The reason why the execution plan appears to be so wrong is because only the first iteration of the recursive CTE is being considered. It's the reason why people sometimes think that recursive CTE's are so good instead of being the resource sucking performance hogs they can sometimes be.
