October 25, 2010 at 5:42 am
Hi,
I've been doing some work on trying to optimize a procedure (Procedure A), so I wrote a new procedure (Procedure B) and looking at the i/oand time stats it is a significant improvement, it also visually appears to run faster.
However when I run the execution plans for them both, the plan suggests that Procedure B has a cost of 80% and Procedure A a cost of 20%, this is contradictory to the i/o and time stats.
The example I have is large so I've not posted it up yet and I've not had time to try and replicate on a smaller scale. So please forgive me not uploading the SQL.
I was just wondering if anyone had experienced a similar thing in the past?
I'll try and collate the stats and execution plans and upload them soon, but any general comments would be welcome.
Thanks,
Nic
October 25, 2010 at 5:48 am
Execution plan costs are estimates. There are several things that can make them completely incorrect.
Bottom line, which is faster? (use Stats time) Which uses lass resources? (stats IO or profiler)
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
October 25, 2010 at 5:49 am
Are both CPU time and elapsed time lower on Procedure B? If it's just elapsed time, it could be generating a parallel execution plan for Procedure B, which although completing in less time could be more expensive.
October 25, 2010 at 6:31 am
HowardW (10/25/2010)
Are both CPU time and elapsed time lower on Procedure B? If it's just elapsed time, it could be generating a parallel execution plan for Procedure B, which although completing in less time could be more expensive.
Thank you both for the replies, in this instance Procedure B has cpu and elapsed times lower than Procedure A, however it does have, at one point, a cpu time higher than the elapsed time.
"SQL Server Execution Times:
CPU time = 32 ms, elapsed time = 23 ms."
This would suggest something running in parrallel I guess.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply