SQL Server Performance Forum – Threads Archive
DBA consensus on statistics collection
Some "experts" suggest leaving auto create and update statistics on and some suggest turning them off and running statistics gathering jobs during periods of known low activity. How are people addressing this in their shops? What are the pros and cons for automatic statistic collection? I have a large system that is heavily accessed by adhoc queries from 6am to 5pm. I have auto create and update statistics off. I rebuild my indexes and gather statistics using a scheduled job during the early am hours.If the data does not change drastically from day to day then leaving statistics off should not have that much of an adverse affect on the optimisers ability to choose a good execution plan.
However, it only takes one query to go slightly askew to affect performance. I work on the basis that the overhead for maintaining statistics in real time is not that much, and so I prefer to leave them on and guarantee that the optimiser is informed enough to make judgements. My advice would be to concentrate on increasing performance in other areas if there is a problem, and only bother turning off statistics if you really need to squeeze yet more performance out of the server, after doing this.
Like Chappy, I believe the overhead is very low. But, in some instances, and perhaps yours in one of them, turning off automatic statistics is useful. This is especially the case for very busy databases that have mostly reads, which sounds like your situation. Also, you may want to research the Profiler event called "Auto-Update Stats." What this event tracks is when auto-update event occur. For example, if you have Auto Update on, and run a trace and look for these events, you will be able to tell how often Auto Update is kicking in. If you find that it is rare, then there is no point in turning Auto Update off, but if there are many, many Auto Update events, then you may be better off by turning Auto Update off. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
You also need to be careful because the auto-update can actually be triggered by a large update (or insert or delete) query. This is because stats are auto-updated when a certain proportion of the data has changed. This means you can end up with update stats running at the same time as a large query, and this can lead to performance problems. Having said that, I set auto-update stats on on all my production dbs with no adverse performance problems. Tom Pullen
DBA, Oxfam GB
]]>