| Erland Sommarskog 2006-03-30, 3:25 am |
| Chris Weston (chrisweston& #91;losethislot]@ntl
world.com) writes:
> Hi. I have automatic statistic update turned on for all my databases. Is
> this an overhead I can do without? Could I update them overnight when the
> database is hardly in use?
Unless you can deduct that auto-stats is causing you performance problems,
I would not consider doing this.
What may be important, though, is if you have large table with monotonically
growing keys, is that you run UPDATE STATISTICS on these tables with some
frequency. This is because auto-stats only sets in when 20% of the rows
have changed. For a 10 million-row table, that means that you need another
two million before autostats set in. Since the key grows monotonically, this
means that the statistics for the newly inserted rows is grossly inaccurate.
If you run a defragmentation job regularly, you don't need to do UPDATE
STATISTICS as well, as when you rebuild the index, the statistics are
updatead automatically. What we ran into was that a colleague set up a
maintenance job that would only defragment indexes with a certainly
level of fragmentation. Tables with a clustered key that grows monotically,
do not get fragmented easily, so we ran into problems with stale statistics.
Our maintenance job now performs UPDATE STATISTICS WITH FULLSCAN INDEX
on tables that don't get defragmented. (We're restricting the stats update
to indexes, because else it took too long time.)
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
|