Statistics in Synchronization and Asynchronization mode to gain the performance.

In SQL Server we have Statistics to improve the performance on tables. Following are some of the concepts about Statistics in Synchronization/Asynchronization mode to gain the performance from tables.

1. Statistics Asynchronization mode was introduced in SQL Server 2005. In earlier versions only synchronizations mode was available. But the default level is synchronization only.

2. Synchronization /Asynchronization options are at database level, not individual object level. There is single option we can enable or disable this for entire database. We can do this by using alter database statement.
Syntax : ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC OFF

3. To checkup this option on existing databases.
Syntax : Select name,is_auto_update_stats_async_on from sys.databases

1 = enabled
0 = disabled

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. Mainly it is using to improve the performance on the tables.

So, prior to utilize the statistics aynchronous option at database level, the following process should do at object level.
1. Create statistics on indexed columns. It is suggestable that statistics must create on composite indexed columns.

2. All the created statistics must be updated regularly. When we update the statistics on regular basis, we can gain the performance from tables.

3. This options will work on tables which are having statistics and updating regularly will improve the performance compare to synchronization mode.