Estimated Execution Plan Curiosity

Question

Post reply

Estimated Execution Plan Curiosity

homebrew01

SSC Guru

Points: 55548
More actions
February 28, 2013 at 9:31 am

#290132

I have a nightly job the runs about 50 insert & update statements and takes a few hours. I ran the whole query's Estimated Execution Plan, and query #35 said it had a 75% cost relative to the batch. It is a simple update, with 2 clustered index scans. I tested the statement originally and it took 33 seconds, added a couple of indexes on the join columns and it took 2 seconds.
But when I run the estimated execution plan again it says that query is now 88% relative to the batch.
Seems odd (to someone not well versed in execution plans)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

I'll let others provide more info, but from what I have been learning, don't always put much credence in those values.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Indeed Lynn is right, cost relative to batch is really "Estimated cost relative to cost of Estimated Batch".

If the actual io is not similar to the estimated io then naturally the Cost relative to batch will be inaccurate.

For instance spills have a massive io cost that are not seen here.

I wouldn't use it for anything other that the roughest of checks.

Use a trace and use the metrics from that to decide which is the costly queries.

Clear Sky SQL
My Blog[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Estimated values are estimates based on what the QO knows of the query. They can be very wrong indeed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

And you really can't use those costs to compare one query to another because of what everyone else has said. Within a given query, you have to use the costs because they are the only measures supplied, especially when looking at an estimated plan. But they're not really meant for comparing one query to a second.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning