Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lazerath
Constraint Violating Yak Guru
343 Posts |
 Posted - 2008-05-16 : 22:18:48
|
I've been working with SQL Server 2005 for a while now and I've noticed some odd behavior that I want to bounce of other members of the community. I should preface that I've been a forum viewer (and occasional contributer) here at SQL Team for a while and I've naturally developed a keen sense for optimizations.Fundamentally, longer stored procedures with perfectly fine/optimized execution plans are inconsistent with real world performance. In some of these cases, a low subtree cost on a 4 core machine with 16gb of ram and 2 15 drive SAS arrays with little load takes excessively long to run or in some cases doesn't complete.This isn't due to blocking or resource bottlenecks as I'm quite familiar with built in tools to troubleshoot and resolve those issues. In all cases, I am able to rearchitect the stored procedure into a higher subtree cost variant and get reasonable performance, but it's frustrating to have to redo work and there seems to be no common theme other than longer multi-statement procedures.I've used SQL Server 2000 extensively and did not notice this level of inconsistency in performance with that product version. Just wondering if others in the community have experiences similar or if I'm just crazy.  Thanks for reading my rant.- Shane |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-16 : 23:26:21
|
| It would be really good if you check your stored procedure with SQL profiler using Funnel technique and finding root-cause analysis like recompiling many times, inappropriate index and the way how you executing it. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-17 : 17:24:54
|
| You should check io stats as well. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-05-19 : 04:13:53
|
| Bear in mind that the costs given in the execution plan are based upon IO, CPU and time, not just time, and the estimated subtree cost given in the graphical view is just that, an estimate. It's calculated at optimisation time based on what the optimiser thinks the query will do. In some cases it is waaaay off, especially if you have scalar UDFs involved.You say these procs are larger. Could you be running into parameter sniffing issues? The exec plan looks optimal, but isn't.If you use STATISTICS IO, what do the IO stats look like?--Gail Shaw |
|
|
|
|
|
|
|
|
Execution Plans Inconsistent with Performance