Geeks With Blogs
Max's Blog .NET, Windows Phone, SQL Server and other exciting stuff

Asynchronous statistics auto update in SQL Server would ensure that the statistics are not updated when a query is executed, ensuring timely response. For this option, we need to set AUTO_UPDATE_STATISTICS_ASYNC to ON and also AUTO_UPDATE_STATISTICS to ON. Here, a separate thread runs in the background, which actually does the job of updating the statistics. So updated stats are available the next time for a query. This can also work the way round, meaning that a query might be using old stats and hence would be taking more time.

Synchronous statistics auto update in SQL Server would ensure that the stats are up-to date, every time a query is executed. This can be very much time consuming and is in proportion with the number of rows in a table. For this option, we need to set AUTO_UPDATE_STATISTICS_ASYNC to OFF.

Wise decisions had to made considering the scenario. For me, it helped me a lot by turning AUTO_UPDATE_STATISTICS_ASYNC to ON. This query below can be used to get the statistics status across all the databases in an instance.

SELECT name AS "Name", 
    is_auto_create_stats_on AS "Auto Create Stats",
    is_auto_update_stats_on AS "Auto Update Stats",
    is_auto_update_stats_async_on AS "Asynchronous Update" 
FROM sys.databases
GO

The below queries can be used to set the ASYNC on.

ALTER DATABASE <DB_NAME> SET AUTO_UPDATE_STATISTICS ON 
ALTER DATABASE <DB_NAME> SET AUTO_UPDATE_STATISTICS_ASYNC ON

This article has been prepared by referring to various other sources in the internet. Here are they:

http://weblogs.sqlteam.com/tarad/archive/2008/06/16/Asynchronous-Update-Statistics.aspx

http://www.sql-server-performance.com/articles/per/asynchronous_statistics_p1.aspx

SQL Server BOL

Posted on Tuesday, November 24, 2009 10:08 AM SQL Server | Back to top


Comments on this post: Synchronous Vs Asynchronous Statistics auto update in SQL Server

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Max | Powered by: GeeksWithBlogs.net