Async Auto Create Statistics Resolves Timeout

The following is based on an actual customer support case:

Customer facing web application executes a specific stored procedure that is set to timeout after 5 seconds. Most of the time the duration of this stored procedure is sub-second, but when the server load is high, this stored procedure will timeout roughly 5% of the time.

Top wait = latch non-buffer

Analyzed extended events capture of query executions filtering on sessions where stored procedure duration > 5 sec and found 4.9 seconds of the duration was spent updating qpstats on a > 100 million row table.

Enabled auto update stats async to resolve the issue:

ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS_ASYNC ON

Statistics updates are synchronous by default which means queries always compile and execute with the latest statistics.  When the threshold has been reached and statistics are stale, the query optimizer will wait for the statistics to be updated before compiling the query plan.  If there are many rows in the table, the wait can be significant. When statistics updates are asynchronous, the query optimizer doesn’t wait but goes ahead and uses the existing query plan (or compiles a new one) using the existing statistics. Now, it is possible that the query optimizer could choose a suboptimal query plan if statistics are out-of-date when the query compiles. Queries that compile after the asynchronous updates have completed will benefit from using the updated statistics.

You should stick with the default statistics updates when you perform operations that change the distribution of data, such as truncating a table or performing a bulk update of a large percentage of the rows. If you do not update the statistics after completing the operation, using synchronous statistics will ensure statistics are up-to-date before executing queries on the changed data.

You should use asynchronous update statistics to achieve more predictable query response times for the following scenarios:

• Your application frequently executes the same query, similar queries, or similar cached query plans. Your query response times might be more predictable with asynchronous statistics updates than with synchronous statistics updates because the query optimizer can execute incoming queries without waiting for up-to-date statistics. This avoids delaying some queries and not others.
• Your application has experienced client request time outs caused by one or more queries waiting for updated statistics. In some cases, waiting for synchronous statistics could cause applications with aggressive time outs to fail.