Improving concurrency of asynchronous statistics update


Executive summary

 

In Azure SQL Database and Azure SQL Managed Instance, the background process that updates statistics asynchronously can now wait for the schema modification lock on a low priority queue. This improves concurrency for workloads with frequent query plan (re)compilations.

 

New behavior is enabled with the ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY database-scoped configuration. This feature is currently in public preview.

 

Automatic statistics update – synchronous vs. asynchronous

 

By default, the SQL Server database engine updates statistics automatically as needed to reflect changes in the underlying data, and help the query optimizer generate more optimal plans. When a query plan