March 22, 2011 at 8:52 am
Hi,
I'm comparing the performance of two similar queries by:
a) comparing cpu, duration, io read, io write using profiler
b) comparing the "query costs (relative to the batch)" of the actual execution plans
Query 1 runs much faster than Query 2 and returns much lower numbers for cpu, duration, io read, io write
The thing that is confusing me is that the "query costs (relative to the batch)" for Query 1 is 76%
How can Query 1 be more costly if it is consuming less resources (and runs faster)???
Thanks
p.s. Query 1 (the fast query) uses a sub query. Query 2 uses row number
March 22, 2011 at 9:52 am
Might be good to attach the plan so that it can be reviewed. Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 22, 2011 at 9:57 am
The costs are estimates, there are a number of things that can make them wrong, cardinality inaccuracies being one of the more common/.
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
March 22, 2011 at 10:04 am
Thanks for the response.
How can the costs be estimates in the actual execution plan?
I thought the actual plan was the plan it used for the query?
March 22, 2011 at 11:04 am
jonwolds 8288 (3/22/2011)
How can the costs be estimates in the actual execution plan?
Because they are calculated at optimisation time only.
I thought the actual plan was the plan it used for the query?
It is. That doesn't mean that compile-time information in the plan (like costs) are updated based on run-time execution statistics. They are not.
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
March 23, 2011 at 4:20 am
Thanks. That answers my question.
So, if I understand correctly, the execution plan costs should be used as a good indication of which parts of a query are ripe for optimisation but the most accurate way of comparing the performance of two queries is to compare IO/Time statistics and/or monitor via profiler?
March 23, 2011 at 4:59 am
The costs can be used that way, but bear in mind that they can be wrong and deceiving. I have a demo (that I did as part of the 24 Hours Of Pass presentation) that shows a query plan where 4 operators are each listed as 25% of the cost, but in reality one is more like 99% of the cost.
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
March 23, 2011 at 5:55 am
And if you toss in something like multi-statement table valued functions the cost can be shown as zero when it is in fact extremely high.
Costs are indicators, but they are not measures.
"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
March 23, 2011 at 11:46 am
From my point of view the query cost in the actual execution plan is even more misleading than the recommended indexes DTA comes up with (comparing apples and oranges, I know...).
Profiler and STATISTICS TIME,IO will provide much more useful information than the actual execution plan in terms of duration and I/O.
Regarding the DTA results: those are at most hints regarding columns / column combinations that might be worth to be considered during index tuning.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply