Geeks With Blogs

News


Add to Google
profile for Max at Stack Overflow, Q&A for professional and enthusiast programmers

Post Categories

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