Auto-Update Statistics

 

For a large majority of SQL Server installations, the most important best practice is to use auto create and auto update statistics database-wide. Auto create and auto update statistics are on by default. If you observe bad plans and suspect that missing or out of date statistics are at fault, verify that auto create and auto update statistics are on. If enable, then check when last updated.  If recently updated, then run-running with FULL SCAN will probably take hours to run and NOT improve query performance.

 

Question:  Do we really need to troubleshooting Query performance by running UPDATE STATS with FULL SCAN when AUTO UPDATE STATS is enable?

 

Answer: Probably Not!

 

1) Use sp_helpdb <database> to see if AUTO STATS are enabled.

 

    sp_helpdb <db name>

    >>>>>IsAutoUpdateStatistics   

 

 

2) If AUTO is enable, verify when STATS were last updated.

 

DBCC show_statistics ('Shippers', 'PK_Shippers') WITH STAT_HEADER

 

Name Updated

------------------------------ --------------------

PK_Shippers Jul 16 2008 11:47AM

 

select stats_date(object_id('Shippers'),1)

-----------------------

2008-07-16 11:47:34.697

 

 

 “Statistics are a histogram consisting of an even sampling of values for the index key (or the first column of the key for a composite index) based on the current data. The histogram is stored in the statblob field of the sysindexes table, which is of type image."  -- Kalen Delaney

 

If STATS were recently updated, then FULL SCAN may take hours to perform on a large table and may not return any more accurate statistics then already collected.  Also, the next time AUTO runs it will overwrite the all the statistics generated from the FULL SCAN.  Rememer, both FULL SCAN and AUTO STATS are updating the same data in sysindexes, so whomever runs last are stats used by the Query Optimizer.

 

Rule of thumb!

--------------------

Use Update Stats with full scan and disable Auto Stats when doing large batch updates and minimal to none ongoing updates.

Use Auto Stats (default) and don't run UPDATE STATS Full Scan when data is modified throughout the day.

 

Additional Information

-------------------------------

In SQL 2000 Auto Update stats monitors the rowmodctr value of sysindexes which records number of data changes. If the number of data changes exceeds a threshold the statistics are updated. When Statistics are updated this value is reset to 0. When statistics are update, Query Optimizer will load a new query plan. When the Optimizer threshold is not reach but data modifications have occurred, Update Statistics (FULL or Partial) will force new statistics and new query plan.

 

In SQL 2005/2008 changes are now tracked by column.  The rowmodctr provides overall indication of data changes to a table/index but it alone doesn't trigger update stats.

 

An alternative to enabling auto create statistics is enabled or make sure to manually create statistics using CREATE STATISTICS or sp_createstats.  Note that auto-statistics will not work for read-only databases.

 

 

This info from ianjo's blog (http://blogs.msdn.com/ianjo/archive/2005/11/10/491549.aspx) and from the “Inside SQL Server 2000" book by Kalen Delaney.

 

--Chris Skorlinski

--Microsoft SQL Server Escalation Services