keywords:
sp_updatestats
auto update stats
statistics
sql server 2000 8.0
8.0.0.760
Windows 2003 SP1

We experience following problems on our SQL-Server 2000 SP3a (SQL Server
8.0.0.760) running on OS Windows 2003 SP1:

It doesent seem that the statistics are auto updated on databases where auto
create stats and auto update stats is set to on.
After a while, sometimes even within one day, we experience severe
performance problems.
The "traffic" (access by selects, inserts...) on the database is definitely
there, so I would understand the performance problems if the statistics
would not be updated at all.
Each time we start sp_updatestats manually the performance regains its
original level. But sometimes we have to do this 10 times a day
and we have no idea why the auto update doesnt work.
Any suggestions would be appreceated.

Sincerly,
Steve Wagner

Steve
Well, you can track this info by using SQL Server Profiler ,how often sql
server updates statistics in case as you said it turned on

Under Events Tab click on Object and select AutoStats




Show quoteHide quote

From my experience there are many cases that you just can not relay on
the server to automatically update statistics.  In those cases you can
create a job that updates the statistics for a specific table, and
schedule it to run few times a day (according to your needs).

Adi
Steve Cartwright wrote:
Show quoteHide quote

Steve Cartwright (ste***@bills.com) writes:
Auto statistics is not a panancea. Works many times, but sometimes they
kick in too often, and sometimes too seldom.

One case which is problematic is large tables with monotonically growing
keys. Say that in the morning the largest id is 123456789. In the
afternoon there is a query that includes the condition

   id > 123456780

The optimizer things that the condition will only return nine rows, but
in fact it now returns 120000 rows and the query performs miserably.

So why wasn't the statistics updated? The statistics for a table is
updated when 500 rows have been modified (for temp tables there is also
a limit of six rows), and then when 20% of the rows have been modified.
If you have a table with 100 million rows, then autostats will kick
very rarely, and in combindation with the monotonic key it does not
work out well.

One strategy to handle this is to run UPDATE STATISTICS on selected table
with some frequency, and possibly also with a higher sampling rate. Another
is to look into partitioned views, so that the rows are inserted into a
smaller table, but partition only to get more frequent autostats, is not
palatable.

This white paper include a lot of good information about statisics:
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

NEWSEQUINTIALID()
Slow Stored Procedure Execution
Join Question
How to use "Not Exists" ?
Generating multiple rows with xml data column