SYSK 161: SQL – the Pros and Cons of Updating Statistics Asynchronously

  • Article

As many of you may know, SQL Server 2005 provides a way to update statistics asynchronously. To my surprise, searching for “AUTO_UPDATE_STATISTICS_ASYNC pros cons” on www.live.com and www.google.com resulted in “We could not find any results…” message… thus, this post.

When the AUTO_UPDATE_STATISTICS database option is set to ON (the default), the query optimizer automatically updates this statistical information periodically as the data in the tables changes. Note: queries started after statistics are updated, may cause the recompilation of cached plans that depend on the older statistics version.

In SQL Server 2005, the database option AUTO_UPDATE_STATISTICS_ASYNC provides asynchronous statistics updating. Note: updating asynchronous statistics cannot take place if any data definition language (DDL) statements, such as CREATE, ALTER, and DROP statements, occur in the same explicit user transaction.

Pros:
- When this option is set to ON, queries do not wait for the statistics to be updated before compiling. The statistics are updating by a worker thread in a background process.
- Because there is no delay for updated statistics, query response times are predictable

Cons:
- If a query is made during the statistics update, it will be using existing out-of-date statistics, which may cause the query optimizer to choose a less-efficient query plan (queries that start after the updated statistics are ready will use the new statistics).
- This option cannot be used to asynchronously create statistics (only update)

As per Microsoft documentation, consider setting AUTO_UPDATE_STATISTICS_ASYNC option in your environment only if your application:

1) Has experienced client request time-outs caused by one or more queries waiting for updated statistics.
2) Requires predictable query response times, even at the expense of occasionally running queries with less efficient query plans because of outdated statistics.

Source: http://msdn2.microsoft.com/en-us/library/ms190397.aspx