This article explores auto-update SQL Server Statistics asynchronous configuration for Azure SQL Database and On-premises SQL instance.
Introduction
Statistics plays a vital role in the query optimizer task to build a cost-effective and optimized query execution plan. The query optimizer uses SQL Server statistics as an informational source to know about the data distribution of the column value(s). Many database professionals focus on indexes for performance optimization. However, if you have correct indexes and improper statistics, SQL Server won’t be able to return output efficiently.
- Note: Never overlook the importance of statistics in query optimization
There are multiple statistics configuration options available for a database professional to use statistics optimally. You should be familiar with statistics before proceeding with this article. You can refer to SQLShack performance category articles to be familiar with statistics, functionality, impact and query performance improvements.
In the article, Persist sampling rate for automatic SQL Server statistics updates, we discussed the threshold for automatically updating SQL Server statistics. The dynamic threshold depends on the table cardinality, i.e. number of data rows.
If we look at the Azure SQL Database properties and navigate options, it has a configuration – Auto Update Statistics asynchronously, in a disabled state (default). This option is similar to an on-premises SQL Server instance.
You can also validate the auto_update_stats_async configuration using the sys.databases query as shown below:
|
1 2 3 4 5 6 7 |
Select CASE is_auto_update_stats_async_on WHEN 0 THEN 'Disabled' WHEN 1 THEN 'Enabled Auto Update Stats Async' End AS AsyncStatsUpdate from sys.databases where name = 'AzureDemoDatabase' |
Environment details
This article uses Microsoft SQL Azure (RTM) – 12.0.2000.8 version for executing queries. If you want to deploy an azure SQL instance, refer to the SQL Azure category on SQLShack as a reference point. You can also utilize an on-premises SQL Server instance for understanding SQL Server statistics.
In this article, we will explore this feature, its usage, and its implications.
Auto Update SQL Server statistics Asynchronously
By default, each SQL Server and Azure SQL Database has enabled Automatic statistics updates. We can verify the configuration using the is_auto_update_stats_on column value of sys.databases.
|
1 2 3 4 5 6 7 |
Select case is_auto_update_stats_on WHEN 0 THEN 'Disabled' WHEN 1 THEN 'Enabled Auto Update Stats' End AS AutoStatsUpdate from sys.databases where name = 'AzureDemoDatabase' |
Due to frequent data manipulation (Update, Insert, Delete), the statistics become stale in an OLTP environment. It restricts SQL Server to prepare an optimized query execution plan, and it might prepare a high-cost plan that impacts your resource utilization and other SQL queries. Using the automatic statistics update, the query optimizer update statistics automatically. Therefore, DBA always focuses on keeping stats up to date.
Let’s understand query optimizer behavior with auto-update state async feature disabled (default) and enabled.
Auto Update Stats Async – Disabled (default)
With the default auto-update SQL Server statistics async property, the query optimizer updates the statistics before it compiles the query. While the stats are being updated, the query optimizer waits for stats update before it recompiles the plan for the new values. It is the synchronous statistics update.
Auto Update Stats Async – Enabled
If we enable the async automatic SQL Server statistics update, the query optimizer does not wait for the query optimizer to update the statistics. It executes the query first and updates the statistics afterward. Due to this behavior, the query does not use updated statistics. SQL Server uses a separate thread for updating stats. However, any new query after the updates uses the latest updated stats.
This feature needs to be enabled at the individual database level. For example, the following query enables Auto_Update_Statistics_Async on the Azure SQL Database.
|
1 |
ALTER DATABASE AzureDemoDatabase SET AUTO_UPDATE_STATISTICS_ASYNC ON |
You can verify it using the below T-SQL script or database properties in SQL Server Management Studio.
This option applies to the statistics created for the indexes or single column query predicates; Stats generated using the Create statistics statement.
- Note: If you have disabled Auto_Update_Statistics for the Azure SQL database, you must enable it before using the auto-update stats async feature
Should we enable the Asynchronous SQL Server statistics feature?
The well-known answer to the question is – It depends!!
If we enable the asynchronous automatic statistics update configuration, SQL Server runs query assuming it has the latest statistics. It might generate a fast execution plan; however, it might create unoptimized query plans due to the out-of-date stats. This feature might help if your data distribution and database workload have less impact on the row counts and distribution.
Suppose you perform a bulk update operation that affects a large number of data rows. SQL Server’s synchronous automatic statistics ensure that the query optimizer works based on the updated statistics on the modified data. However, if you have enabled asynchronous stats update, the query plan (using the old statistics) might create performance issues as it does not have information about recent bulk updates.
Similarly, the asynchronous statistics might increase concurrency issues due to locks blocking. As highlighted earlier, SQL Server uses a background request for async stats update. During the process, it acquires schema modification lock on the statistics metadata. If any session is using the lock, it blocks the async update. On the other hand, this async update session can block different sessions requiring schema stability (Sch-S) lock on metadata objects.
You can use the async stats update feature based on the specific requirements. For example, you want to improve query response time for an extensive OLTP database and do not want SQL Server to wait for update statistics (synchronous update). You must understand the consequence of enabling this. You should not enable it directly on the production database. If you want to use the asynchronous statistics update, test it thoroughly in a development environment with a workload similar to production.
Azure SQL Database behavior for async automatic SQL Server statistics update
In the Azure SQL Database, we can enable database scoped configuration – Async_Stats_Update_Wait_At_Low_Priority, to avoid the concurrency issues of the asynchronous statistics update.
You can execute the following script to check the default value of the database scoped configuration – Async_Stats_Update_Wait_At_Low_Priority
|
1 2 3 |
SELECT [Name], value FROM sys.database_scoped_configurations WHERE name='ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY' |
The value 0 denotes that by default, azure SQL Database does not use the Async_Stats_Update_Wait_At_Low_Priority feature.
To enable it, use the ALTER DATABASE SCOPED CONFIGURATION statement as shown below:
|
1 2 3 4 5 6 |
ALTER DATABASE SCOPED CONFIGURATION SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = ON Go SELECT [Name], value FROM sys.database_scoped_configurations WHERE name='ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY' |
Using this option, Azure SQL Server waits for the background request to acquire schema modification lock. It persists the updated statistics using the low-priority queue. It allows requests to compile queries with current statistics. Suppose the background request cannot acquire the lock. It gets a timeout that results in asynchronous statistics task abortion. In this case, statistics need to be manually updated, or it needs to wait for another update statistics trigger.
- Note:
- This database scoped configuration is available only for Azure SQL Database and Azure SQL Managed Instance. If you plan to use it for an on-premises SQL Server, don’t worry. It is planned to appear in upcoming SQL Server releases
- SQL Server always uses synchronous statistics update for the local temporary table. It does not have any impact due to AUTO_UPDATE_STATISTICS_ASYNC configuration
- SQL Server global temporary tables statistics works depending on the AUTO_UPDATE_STATISTICS_ASYNC value in the user database
Conclusion
In this article, we explored SQL Server statistics configuration for automatic stats update asynchronously. The default configuration for async stats is disabled. Therefore, each environment requirement is different; therefore, we cannot have general guidance for asynchronous statistics updates. However, it is not required in most OLTP environments.
You must not enable it until you have special requirements. Always test the configuration on a lower environment, understand the impact and plan it on the production database.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023