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 |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2006-12-29 : 18:01:22
|
| Guys,I am trying to figure out what is the best way to update statistics. Currently, all of our databases have a default setting of "Auto Update Statistics" and "Auto Create Statistics" ON. Having these features, I believe, tells the server to update/create statistics any time it sees fit.I am curious to know how exactly the server makes the decision when to update statistics and in doing so does it take the current "busy-ness" level of the server as a parameter in its decision making?If the "busy-ness" level is not taken in as a parameter, it implies that statistics could be updated during the busy time of the server. In that case, wouldn't it be better to set the default OFF and then create a job and schedule it to run at times we know the server is not busy?Thanks a lot |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-29 : 18:33:07
|
| Unless you are seeing performance problems I'd recommend leaving them on auto. The performance impact of creating and updating is not usually a problem and having missing or out of date statistics will probably be worse for performance than the potential impact of maintaining them. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-30 : 03:04:48
|
| We do a full rebuild of all statistics every night. We also have "Auto Update Statistics" ON in case something during the day causes SQL Server to decide something needs freshening up.Kristen |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2007-01-02 : 02:46:43
|
| Hmm, put it this way, its recommanded not to have it on and do it manually. Since yes, the update stats can happen anytime even during your server most busy period, and yes i would say 90% of the time it will be doing it at the most busy time. Since that is when it see the most updates and it will go ahead and updates it. So its always better to have it off. But for some small DB, the performance hit might not be that bad, so ppl will just leave it. unless you got a big DB, even only rebuild the index over weekends and not do any (or only once on Wed night) is enough for most common DB.As for when it will perform the update: * If the number of rows in a table are greater than 6, but less than or equal to 500, then statistics are automatically updated when there have been 500 modifications made. * If the number of rows in the table are greater than 500, then updates are automatically made when (500 plus 20 percent of the number of rows in the table) have been modified. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-02 : 02:54:20
|
"or only once on Wed night"If you have sufficient "quiet time" every night I see no reason not to run it daily. Even if the effect is only a small tune-up it is beneficial to the users.If the system is 100% utilised 24/7  then different rules will apply ...Kristen |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-01-02 : 05:17:10
|
| generally, it is acceptable to keep autoupdate stats enabled for all databases. I can think of a few situations where I might disable it though:1. you have a VLDB (500GB in size or greater) and you notice performance problems when the autostats are running, or you have old stats in place (autostat threshold hasn't been hit).2. You have a very busy system with hundreds or even thousands of transactions per second and autostats is hurting performance.3. You have a data warehouse. The first few examples are pretty self explanatory. You probably would need to figure out the best time during the day or week to do general database maintenance anyway in those types of situations. Also, my experience has been that the algorithm used for kicking off autostat generation works well for small databases, but not for VLDB. I have had many situations with a VLDB where the stats were outdated on large tables/indexes causing performance issues.The datawarehouse thing is pretty straightforward too. I would disable autostats on a DW and simply rebuild the stats after each data load. Not a big deal though in this situation since this isn't a transactional system and autostats aren't getting in the way in most cases, and it is generally accepted that most DW implementations have some kind of nightly unavailability due to data loads anyway.So, it is fine to leave autostats in place on most every database. I would also create a job that completely rebuilds stats and put that in place as part of your weekly or monthly maintenance. Also, make sure you schedule your manual statistics updates during a time that the data in your DB is representative of what is typically there. For example, don't run the stats job after truncating some lookup tables (and prior to reloading them).-ec |
|
|
|
TRACEYSQL
Aged Yak Warrior
594 Posts |
Posted - 2007-04-08 : 17:58:39
|
| Hi there On maintenance plans i just increased the optimization update statistics used by optimizer to 40 every night. I was about to turn it off on the database and got auto create statistics and auto update statistics Got confused which one to turn off. Our system is extremely busy used all day long with many transactions Which one do i turn off.....during the day. If i turn if off i want to make sure that i do it at night with a script ....which script would this be dbcc updatestatistics ('dbname') Thanks |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-09 : 16:06:25
|
| Turn off 'auto update statistics'. |
|
|
|
|
|
|
|
|
Updating statistics - automate or not?