Dynamically Updating Statistics on all Databases, With Sweet Potato Fries.

Sorry, I lied about the sweet potato fries. But this script will allow you to update statistics with options. Still interested? 

This is one of the steps of my customized maintenance plan to update statistics on all databases on a server. 

Overview of statistics in SQL Server, sample sizes and auto update information can be found on many sites. I like Grant Fritchey's article, SQL Server Statistics Questions We Were Too Shy to Ask.

Also the algorithm for determining the sampling rate to use for auto update statistics can be found here: SQL Server Statistics: Explained

Specifically:

Auto Update stats Algorithm: 

So the Auto Update stats will fire for every 500 + 20% change in table rows. Of course, we have an improved algorithm in SQL 2012 which is SQRT(1000 * Table rows) which is much better.

When it fires it will use the  default sampling rate and here is the algorithm how it calculates the sampling rate.

1)      If the table < 8MB then it updates the statistics with a fullscan.

2)      If the table > 8MB, it follows an algorithm. It reduces the sampling rate as the number of rows in the table are increased to make sure we are not scanning too much data. This is not a fixed value but is under the control of optimizer. It is not a linear algorithm either.

Example: if we have 1,000,000 rows it would use a sampling rate of 30% but when the number of rows increase to 8,000,000 it would reduce the sampling rate to 10%. These sampling rates are not under the DBAs control but optimizer decides it.

Customize these aspects, to fit your needs:

• I have time in my maintenance window to perform FULLSCAN instead of the standard sample, but you can change this to a sample percent size, or drop the "WITH FULLSCAN" in the script to use SQL Server standard sample size.
• I have defined a statistic as being "out of date" if RowModCtr / RowCnt is greater or equal to 2, so 2% of the row count changes, according to the "guesstimate" of RowModCtr. I say guesstimate because RowModCtr is not a true count of changes to the row, if you are using SQL 2005 or later (Books online).
• I also update statistics, regardless of how many changes to the table have occurred, for stats older than 30 days. You can change that time frame.

Disclaimer: this script is based on one found on DBA Stack Exchange as it most closely fit my needs.