Update Statistics Job for Temenos T24

According to the Optimizing Windows and SQL Server for Temenos T24 whitepaper it is recommended to keep the AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS databases options on.  The whitepaper also recommends creating a maintenance plan to update statistics. 

Q:   If AUTO_UPDATE_STATISTICS is enabled why do you need to manually update statistics using a scheduled job?

To fully understand the answer to this question we need to discuss what AUTO_UPDATE_STATISTICS does. 

A : "When the automatic update statistics option, AUTO_UPDATE_STATISTICS, is on, the query optimizer determines when statistics might be out-of-date and then updates them when they are used by a query. Statistics become out-of-date after insert, update, delete, or merge operations change the data distribution in the table or indexed view. The query optimizer determines when statistics might be out-of-date by counting the number of data modifications since the last statistics update and comparing the number of modifications to a threshold. The threshold is based on the number of rows in the table or indexed view. "

Q:   What is this threshold?

The following MSDN Article provides a detailed description as to how the threshold is determined but for the sake of this discussion we can summarize it with the following statement: 

A:   AUTO_UPDATE_STATISTICS will engage when the table has grown/changed by 20% + 500 rows.

So now if we go back to the original question being discussed in this post...

Q:   If AUTO_UPDATE_STATISTICS is enabled why do you need to manually update statistics using a scheduled job?

Let's take what we've learned above and apply this logic to a table with 500,000,000 rows. 

How many rows would have to be inserted, updated, or deleted in order for the AUTO_UPDATE_STATISTICS option to engage? 10,000,500 (20% + 500 rows)

Depending on the throughput of your table and the amount of data that would need to change your statistics can become out of date and lose their usefulness before the AUTO_UPDATE_STATISTICS option will engage.  The solution to this is to supplement the AUTO_UPDATE_STATISTICS option with a manual updating of statistics as described in Appendix 3: Update Statistics in the Optimizing Windows and SQL Server for Temenos T24 whitepaper.

Statistics are critical for the query optimizer to determine the "plan of attack" (execution plan) when returning results from your query.  Bad statistics lead to bad execution plans so it is crucial to keep your statistics up to date for optimal performance of your databases.