Thursday, January 25, 2007

AUTO_UPDATE_STATISTICS_ASYNC Database Option in SQL Server 2005

SQL Server 2005 supports a new option called AUTO_UPDATE_STATISTICS_ASYNC which can be used to fine tune your database. Normally, when an executing query triggers an automatic updating of statistics through the query optimizer , the query has to wait until the statistics are updated before proceed further . in other words it can also called synchronous operation. But When AUTO_UPDATE_STATISTICS_ASYNC option is set to ON, queries do not wait for the statistics to be updated before compiling. Ie. it is an asynchronous process Instead, the out-of-date statistics are put on a queue for updating by a worker thread in a background process. The query and any other concurrent queries compile immediately by using the existing out-of-date statistics. Because there is no delay for updated statistics, query response times are predictable; however, the out-of-date statistics may cause the query optimizer to choose a less-efficient query plan

Note : AUTO_UPDATE_STATISTICS_ASYNC option has no effect if the AUTO_UPDATE_STATISTICS database option is OFF

Refer :


Post a Comment

Subscribe to Post Comments [Atom]

<< Home