Update Statistics – Synchronously and asynchronously. Which one is better ?

Download PDF – Update Statistics – Synchronously and asynchronously. Which one is better?

We can Update Statistics synchronously and asynchronously.

Default is Synchronous

How we can enable this option-

--

ALTER DATABASE [Database_Name] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [Database_Name] SET AUTO_UPDATE_STATISTICS_ASYNC ON 

--

Synchronous Case-

In this case SQL Server first update the statistics ( Of course if they were outdated ) and then execute the query. So depending on the table size and the other processes running on the server update statistics may take some time and slow down you query execution. In this case your query has to wait till the time update statistics operation is not completed. In this case all the queries current and future will get the benefit of the statistics update.

Asynchronous Case-

In this case SQL Server first execute the query and then update the statistics ( Of course if they were outdated ). In this case your query will NOT have to wait till the time update statistics operation is not completed. In this case statistics update are handled by a background process. The query will not get the benefit of the statistics update, however future queries will.

Note 1- Enabling the async option affects will not allow yout 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.

Note 2-To check which databases have this option enabled, we can check out the is_auto_update_stats_async_on column in sys.databases.

Script to check which database has which option enabled.

--

SELECT name Database_Name,
      is_auto_create_stats_on [Create_Stats]
     ,is_auto_update_stats_on [Auto_Update_Stats]
     ,is_auto_update_stats_async_on [Async_Update]
FROM sys.databases
GO

--

That’s all folks, I hope you’ve enjoyed learning about “Sync / Async” option to update statistics, and I’ll see you soon with more “Performance Tuning” articles.

Thanks !

Pawan Kumar Khowal

MSBISkills.com