auto update statistics

auto update statistics

Post by derek.beaco » Thu, 17 Oct 2002 05:30:12

I've been scanning the net to try and find out how and when sql7 does the
auto update statistics. We're getting some blocking on our server and the
waitresource column in sysprocesses is showing as syscolumns. The only thing
I can think of is that an insert/update or delete has been performed and sql
server is updating the indexes or statistics for the given table. Would this
make any sense or am I way off base?

Does auto update statistics wait until there is a quite period before
updating the statistics?


auto update statistics

Post by Michael Bourg » Thu, 24 Oct 2002 02:39:17

> Does auto update statistics wait until there is a quite period before
> updating the statistics?

I do not believe so - the latest sql-server-performance newsletter
mentioned that.  IIRC, it runs every 30 minutes or so, and can impact
performance, so I assume it's not waiting for a quiet period.



1. Auto create/ auto update statistics, server 7.0

        I was inspired by another posting to this NG and checked the statistics
on a few tables in my DB. I am having problems for quite a long time
that SQL server sometimes chooses very stupid execution plans. I found
out that statistics on one of the very frequented tables was updated
last time at the end of March. But auto create/update statistics is
turned on. Is this normal? At what moments SQL server decides to update
the statistics? Should I rather schedule the job that would force an
update of the statistics at that moment?
        I could hardcode the execution plan into my SQL queries, but there are
those rare moments when SQL server comes up with really good plans (even
better than mine :) and I also do not fancy the idea of writing hints
into the code.


2. Replication overhead

3. Indexes created when auto update statistics is set to On

4. the need of scope

5. auto update statistics

6. Executing an EXE file inside a MDI form

7. Auto Update Statistics

8. Full text/keyword search and retrieval?

9. When are Stored Procs Recompiled if using Auto update Statistics

10. Auto Update Statistics.

11. Is auto-update statistics really necessary?