Rajendra Gupta
Azure SQL Database options

Auto Update SQL Server Statistics Asynchronous configuration for Azure SQL Database

September 7, 2021 by

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.

Azure SQL Database options

You can also validate the auto_update_stats_async configuration using the sys.databases query as shown below:

Validate automatic stats update async properties

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.

Database lab environment

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.

Auto Update SQL Server statistics Asynchronously

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.

You can verify it using the below T-SQL script or database properties in SQL Server Management Studio.

Enabled or disabled auto update stats

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

The value 0 denotes that by default, azure SQL Database does not use the Async_Stats_Update_Wait_At_Low_Priority feature.

Azure SQL Database behavior

To enable it, use the ALTER DATABASE SCOPED CONFIGURATION statement as shown below:

Low Wait 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:
    1. 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
    2. 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
    3. 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.

Rajendra Gupta
Azure, Performance, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is 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 his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

153 Views