Maintaining Statistics in 7.0 & 2000

Question

Post reply

Maintaining Statistics in 7.0 & 2000

Leeland

SSCrazy Eights

Points: 8959
More actions
June 9, 2008 at 9:18 am

#72172

Hi all,
I have a general question about maintaining statistics as the current employer doesn't really rely on them to greatly in our older systems...
I get two different views from co-workers regarding the "auto creation" and "auto update" of statistics...
Most servers we maintain that are SQL 7.0 or SQL 2000 have both options disabled...
When I ask why or what the reasoning behind this is I get answers like...
Cause you can't determine when they get updated...
Causes performance issues at inopportune times...
ect...
basically a lot of negative reasons...
I don't have a ton of experience with maintaining statistics personally so I can't get into a huge debate with the co-workers...and I want to hear what others do...
Do you attempt to maintain them manually? If so how do you do it?
Everything I have read says they are a very important part of the SQL query engine and should be maintained for optimal performance.
The only personal experience I have is troubleshooting some performance issues for a couple of developers...when I checked the execution plans...ect I obviously got a big RED "statistics are out of date...
I ran "sp_updatestats" and performance jumped dramatically...I took those results to the head man (my boss) for discussion and he replied back to me that it may help with "certain" queries 😀
So basically I am trying to figure out how to construct a nice presentation on:
1. why they are important and a decent way to prove that to someone...
2. a solid way to maintain statistics (if there is a way) since no one I work with appears to trust the "auto create and update" options within SQL or thinks they are more work than they are worth.
Thanks for reading and any replies.
Leeland