For the past several months, we have been overwhelmed with performance issues on one particular system.  We know what is causing it, but it is going to take time to modify the code, test the changes, and then deploy to production.  In the meantime, we made several configuration changes that helped performance.  Two of the changes were adding data files to the tempdb database and enabling the asynchronous option of automatic statistics updating (AUTO_UPDATE_STATISTICS_ASYNC). 

I covered our tempdb change in a previous blog.  In it, I described how to optimize tempdb and also provided a script to add the tempdb data files based upon the number of CPU cores.  I ran the script on each of our production instances and not just on the one where we are experiencing problems. 

Now let's talk about the async auto update stats change.  In previous versions of SQL Server, the auto update stats happened synchronously only.  In SQL Server 2005, we have the ability to do it asynchronously as well.  The default is synchronous.

How are the two options (sync and async) different?  When the setting is off and a statistics update is initiated due to out-of-date statistics in the execution plan, the query must wait until the statistics update is complete before compiling and then returning the result set.  When the setting is on, the query does not need to wait as the statistics update are handled by a background process.  The query will not get the benefit of the statistics update, however future queries will.

In order for the option to work, AUTO_UPDATE_STATISTICS must be enabled as well.  It will allow you to enable AUTO_UPDATE_STATISTICS_ASYNC without AUTO_UPDATE_STATISTICS being enabled.  I think it should at least warn you in case you forgot to enable AUTO_UPDATE_STATISTICS.

Here is how to enable the option:

ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON

Enabling the async option affects your ability to put a database into single-user mode.  The option must be disabled to put a database into single-user mode as the async option uses a background thread which takes a connection in the database. 

To see which databases have the option enabled, check out the is_auto_update_stats_async_on column in sys.databases.