x

Execution plan bug??

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

more ▼

asked Mar 01, 2010 at 08:45 AM in Default

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest

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.

more ▼

answered Mar 01, 2010 at 09:51 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 01, 2010 at 09:11 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

working out the cost on the raw data sounds like a good idea - what do I do, how do I do it??!
Mar 01, 2010 at 10:19 AM Fatherjack ♦♦
@Fatherjack - I can't remember off hand - I'll try and remember to email you later!
Mar 01, 2010 at 11:03 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Mar 07, 2010 at 12:47 AM

Jeff Moden gravatar image

Jeff Moden
1.7k 3 4 8

Jeff, this is really interesting but the code doesnt run as you have provided it:- I've had to add code to create the Tally table. Could you provide a link to the (blog article??) documentation mentioned in the comments please? Thanks for the confirmation that the query plan is not always accurate. I was not wholly surprised but the magnitude of the error was totally unexpected.
Mar 08, 2010 at 06:03 AM Fatherjack ♦♦
Ah... my bad. I've corrected the header. The code was originally sent to Grant Fritchey when he was writing one of his books and I forgot to change the header. The "additional explanations" are mostly included (but greatly simplified) in the non-code text in the post above. I also added a comment so people can find the Tally table article to build one.
Mar 09, 2010 at 02:07 AM Jeff Moden
Thanks for the update, Sorry, I've only now come back to this. I like your proof and I'm not surprised really. What did raise my eyebrows was that the est % was over 92000% - I'd have thought it would just be 100% wrong if anything, not mathmatically impossible. Thanks again.
Mar 22, 2010 at 03:05 PM Fatherjack ♦♦
heh... I'm a little bit behind myself. Thank you for the feedback.
Mar 30, 2010 at 09:59 PM Jeff Moden
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1949
x986
x41

asked: Mar 01, 2010 at 08:45 AM

Seen: 2109 times

Last Updated: Mar 01, 2010 at 09:53 AM