26 performance tuning questions and solutions, bad statistics, How to tune SQL queries, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, SQL Complex Queries, SQL Interview questions SQL questions SQL SERVER Interview questions SQL SERVER Interview questions Download SQL SERVER Interview questions Free Download SQL SERVER Interview questions SQL SERVER Tips, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL SERVER Interview questions, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL Tuning Overview, statistics, Statistics in SQL Server, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, Update Statistics - Synchronously and asynchronously., When one should update statistics?, Why we should update statistics?
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 --
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.
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.
Pawan Kumar Khowal