THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

WHY ISN'T AUTO_UPDATE STATISTICS RUNNING?

I find that auto_update statistics in SQL Server is a really good thing.  Without it, many third-party applications would simply fall over from lack of preventative maintenance.  With it, they are able to run for extraordinarily long periods of time without really needing a full-time DBA to check up on the databases.

 

Having said all of that, auto_update statistics is a mystery for many of us.  This is reflected in the fact that I get lots of questions about auto_update statistics when I speak publicly (which seems to be at least once per month these days).  The most common question is “why isn’t auto_update statistics running?”

 

There are many elements to the answer to this question.  Naturally, you’re not going to have any new statistics in a database that is marked as readonly.  However, you might not know that approximately 20% of a table needs to change before triggering an auto_update statistics run for the given table.  If the table is really big, it’s possible that you’re simply not changing enough data.

 

Also remember that statistics are not the same in SQL Server 2005 as they are in SQL Server 2000 and that they must be upgraded using the sp_updatestats stored procedure after you migrate from version 2000 to 20005.  Otherwise, the old statistics are unusable by the query engine.  Once you’ve run sp_updatestats, the engine will maintain them automatically.  Be sure to check the article “After Upgrading the Database Engine” at http://msdn2.microsoft.com/en-s/library/ms143695.aspx to make sure you covered all the bases.

 

Thanks to Connor Cunningham and Simon Sabin (http://sqlblogcasts.com/blogs/simons) for help on this post.

Published Monday, May 19, 2008 11:58 AM by KKline

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Denis Gobo said:

Kevin,

Also don't forget that in 2005 (and up) you can Auto Update Statistics Asynchronously.

Usage: AUTO_UPDATE_STATISTICS_ASYNC { ON OFF }

I blogged about this a while back here: http://sqlservercode.blogspot.com/2006/11/auto-update-statistics-asynchronously.html

May 19, 2008 12:07 PM
 

aspiringgeek said:

The optimizer thrives--or dies, depending on updated stats.

I recommend creating a job to run sp_updatestats prior to busy production periods 1) to avoid the problem cited by Kevin of big tables not reaching the triggering threshold, & 2) to prevent an autoupdate stats event from interrupting production processes.  Even on multi-terabyte DBs, if the h/w is sufficient this takes only a few minutes.

I concur with Denis's recommendation of giving consideration to enabling the new async option.

May 19, 2008 12:46 PM
 

Alejandro Mesa said:

You can also take advantage of the trace flags 2389 and 2390, which enable automatically generated quick statistics for ascending keys.

Ascending Keys and Auto Quick Corrected Statistics

http://blogs.msdn.com/ianjo/archive/2006/04/24/582227.aspx

FIX: You may notice a large increase in compile time when you enable trace flags 2389 and 2390 in SQL Server 2005 Service Pack 1

http://support.microsoft.com/kb/922063/en-us

FIX: SQL Server 2005 may not perform histogram amendments when you use trace flags 2389 and 2390

http://support.microsoft.com/kb/929278/en-us

A list of the bugs that are fixed in SQL Server 2005 Service Pack 2

http://support.microsoft.com/kb/921896/en-us

Cheers,

AMB

May 19, 2008 1:01 PM
 

Jason said:

Have you found a workload that benefits from those trace flags? My experience has been more overhead than help.

In addition to the previous links, this is my favorite 2005 whitepaper:

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx

May 19, 2008 1:13 PM
 

Uri Dimant said:

Hi Kevin.

It is very good subject. BTW , the link you have posted does not work , it shows "Content not found"

It is also inrtesting why you did not mention UPDATE STATISTICS command , are there any differences between both?

May 19, 2008 11:46 PM
 

jerryhung said:

It was in SQL Server Central Question of the Day last week

http://www.sqlservercentral.com/Questions

Q: What would you use to update a single table's statistics in the shortest time?

A: Sp_updatestats does not have an option to update a single table's statistic. It will update all tables in the database. This answer is incorrect. UPDATE STATISTICS can be used with single table and therefore it will run in most cases faster.

http://msdn.microsoft.com/en-us/library/aa260645(SQL.80).aspx

May 20, 2008 10:10 AM
 

Uri Dimant said:

Jerryhung

Yes , actuallY I knew what differences are. My question was if i have to choose between two ( run on all tables) in terms of performance what command is better one?

May 21, 2008 12:57 AM
 

jerryhung said:

Sorry I misunderstood you

Yes I'd be interested to know difference (speed) between the 2 as well

Although I ran with sp_updatestats on our copy of Production database, everything was done less in 5 seconds (our biggest DB is 1.5GB only anyway) so it may not be of much concern for us at least

May 21, 2008 10:02 AM
 

Scott R. said:

Jerry,

One apparent difference with SQL 2005 is that when sp_updatestats is cycling through the stats updates for each table / index, it decides to perform a specific stats update only if the enough data has changed (rowmodctr is large enough) to warrant the stats update (see Books Online excerpt below):

_____

In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.

_____

Based on the wording in Books Online, I assume that this feature is new to SQL 2005, but I don't know this for a fact.

Along with your database sizes, schema complexity, and DB content volatility since the last stats update, this might account for why sp_updatestats ran fast on your system.  Just a guess.

In contrast to sp_updatestats using the rowmodctr to determine whether a given statistic should be updated, I couldn't find similar wording in the Books Online for Update Statistics, leading me to think that Update Statistics is unconditional and will run in every case (regardless of the level of update since the last statistics update).  A deduction, but not a fact.

As you mentioned earlier, sp_updatestats can only be used to update all tables / indexes in a database, while Update Statistics requires a table name (can't do all tables in one Update Statistics command) and the index name is optional (present for a single index statistics update, or absent to update statistics for all indexes on the given table).  Update Statistics could be used in a T-SQL loop or with the undocumented system stored procedure sp_MSforeachtable to process all tables in a database in a single "command" (one invocation of sp_MSforeachtable leading to multiple invocations of Update Statistics without an explicit loop).

Looking at the code for sp_updatestats (use sp_helptext sp_updatestats), it appears that it uses Update Statistics to do the individual statistics updates by table / index, so there should not be any significant performance difference over separately using multiple Update Statistics commands for all tables and indexes (other than the previous mentioned feature in sp_updatestats to skip status updates if insufficient changes were noted).  The conditional logic in sp_updatestats that does this check is:

select @is_ver_current = stats_ver_current(@table_id, @ind_id)

-- note that <> 0 should work against old and new rowmodctr logic (when it is always > 0)

-- also, force a refresh if the stats blob version is not current

if ((@ind_rowmodctr <> 0) or ((@is_ver_current is not null) and (@is_ver_current = 0)))

It appears to use both an old way of determining changes (rowmodctr <> 0) and the result of the stats_ver_current function for the current table and index.  I don't know enough about the stats_ver_current function to comment at this time.

Scott R.

May 21, 2008 1:36 PM
 

Ranga said:

It will be nice if we can change the 500+20% formula so we can have the auto update stats more often / less often

May 22, 2008 11:53 AM
 

Ranga said:

Update Statistics on individual tables is much preferred in large database....it once happenned that we had to do a huge data load and updatestats was running, this is on a 500GB+ db, killing that job took almost 2-3 hrs for rollback! So Update statistics on individual tables is better, can be a quick kill if needed.

May 22, 2008 1:23 PM
 

Jagdev said:

Does auto update stats depend on number of rows also??

July 29, 2010 7:11 AM
 

KKline said:

Jagdev,

There's a basic formula that the table must have experienced enough changes to warrant the update_stats process.  Your predecessor in the comments, Scott R, does a nice job documenting those rules - it's not just number of rows changed.  

-Kev

August 12, 2010 8:03 AM

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement