Performance Issues with UPDATE STATISTICS WITH FULLSCAN on _WA_Sys auto statistics

Performance Issues with UPDATE STATISTICS WITH FULLSCAN on _WA_Sys auto statistics

Post by UrdaM » Sun, 24 Jun 2001 02:05:42

Is there an issue with UPDATE STATISTICS tablename with FULLSCAN when
there exists a large number of  _WA_Sys_(columnname)_(guid) auto
statistics attached to the table??  If one does this command against
an extremely large table and then performs a DBCC SHOW_STATISTICS
(tablename, _WA_Sys_(columnname)_(guid)) for each of these auto
statistics in order, the Updated time differences between each of
these auto statistics is significantly higher than the clustered index
or any of the non-clustered indexes.

If I run a script to only run UPDATE STATISTICS tablename indexname
WITH  FULLSCAN,  I get extremely reasonable times, say fewer than 5
mins. However an UPDATE STATISTICS tablename autostatsname WITH
FULLSCAN can easily take 2 to 3 times to perform. Is some internal
processing occurring that causes such an extreme overhead??




1. Updating statistics for all tables FULLSCAN or SAMPLESCAN


I have a small problem, and maybe somebody here can help me in telling me
the sollution. ;-)

We have a table in the database with 600.000 lines in it.
We do not auto-update the statistics here, but every night we run the UPDATE
STATISTICS with FULLSCAN on this table.
This takes up to 2 hours for the statement to complete.

We want to go to a UPDATE STATISTICS that uses SAMPLESCAN, but how do I know
if the statistics in that table are ok. Because in the past we got bad
statistics and some queries on this table did a tablescan instead of an
indexed scan. And performance of the database collapsed.

Any info on where I should look? I need 'proof' that the indexed values are
just as good as the FULLSCAN values.


2. DTC Erro

3. Auto Create/Update Statistics and insert performance in SQL 7.0

4. how to hide password in perl CGI script?

5. Auto create/ auto update statistics, server 7.0

6. D3 <--> Java, PHP, Python, Perl....

7. auto update statistics

8. SQL 6.5 on Windows XP?????

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

10. auto update statistics

11. Auto Update Statistics