Navigating Asynchronous Statistics Updates in SQL Server 2022

Keeping database statistics updated is crucial for the smooth functioning of your queries. The database engine is pretty smart; it automatically refreshes these statistics to stay in sync with changes in your data. This is key for the query optimizer, the brain behind the scenes, to churn out the most efficient plans for your queries.

While up-to-date statistics often improve query plan quality, the extra time added to some query executions due to statistics update may be undesirable, particularly in transactional workloads with short queries, where updating statistics may take longer than query execution itself. For this reason, the SQL Server database engine also supports an option to update statistics asynchronously. When the database option AUTO_UPDATE_STATISTICS_ASYNC is set to ON, query optimizer proceeds to compile and execute the query even if statistics are considered stale; however, stale statistics are then updated on a background thread asynchronously, so that future query executions starting after this asynchronous process has completed can benefit from up-to-date statistics.

Imagine this: you run a query, and SQL Server takes a quick peek at the statistics. If they’re a bit outdated, it promptly gathers fresh data and updates the database’s metadata. Now, this usually happens in real-time, while your query is running. But here’s the catch – collecting and writing these new stats can add a few extra seconds to your query’s execution time.

Now, for most scenarios, having up-to-date stats is like having a secret weapon for better query performance. However, in certain cases, especially with quick, transactional queries, this update process can feel like a bit of a drag.

To tackle this, SQL Server offers a neat feature – the option to update statistics asynchronously. Flip a switch (the AUTO_UPDATE_STATISTICS_ASYNC option), and your query optimizer won’t wait around for fresh stats. It’ll go ahead with the query compilation and execution, even with stale statistics. Meanwhile, those statistics are quietly updated in the background, ready to enhance future queries.

But, it’s not all sunshine and rainbows. Asynchronous stats updates have their own quirks, especially when it comes to the nitty-gritty of query plan compilation. Let’s dive a bit deeper.

When SQL Server compiles a query plan, it puts protective locks (called Sch-S locks) on the statistics it’s using. These locks are like a “do not disturb” sign, ensuring the stats stay put during compilation. But, if SQL Server spots stale stats during this process, it triggers a background update. This background task then tries to update the stats but has to wait its turn, as the original query plan compilation has the lock.

Here’s where it gets tricky. This waiting game can create a traffic jam of sorts. New queries lining up for compilation also want to put locks on the same stats, but they get stuck waiting behind the background process, which is waiting for the original query. It’s a bit of a block party, and not the fun kind. This can lead to some concurrency issues, affecting the overall performance of your applications, as illustrated in Figure 1.

Improving concurrency of asynchronous statistics update – Microsoft Community Hub

Figure 1. An illustration of a blocking chain forming when asynchronous statistics update is waiting for a Sch-M lock at normal priority.

In SQL Server, the ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY database-scoped configuration plays a crucial role in optimizing the behavior of background processes that update statistics. This configuration modifies the lock acquisition strategy for the schema modification (Sch-M) lock during the asynchronous update of statistics.

When this setting is enabled, the background process tasked with updating statistics adopts a low-priority wait strategy for the Sch-M lock. In practical terms, this means that the process will not attempt to acquire the Sch-M lock on the statistics metadata object until all other processes holding incompatible locks (such as schema stability locks, or Sch-S) have released them. This ensures that the statistics update process does not preemptively interfere with other processes, particularly those involved in query compilation, which are likely to hold Sch-S locks on the same statistics metadata object.

This low-priority waiting mechanism aligns with strategies used in other SQL Server operations, such as online index rebuilding and partition switching. These operations similarly employ low-priority waits to minimize their impact on concurrent transactions and to maintain overall database performance and concurrency.

Improving concurrency of asynchronous statistics update – Microsoft Community Hub

To provide a technical illustration of this mechanism, the Appendix section includes a demonstrative example. This example showcases the effective management of lock prioritization in SQL Server, specifically how the ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY setting enables a more harmonious and less intrusive execution of background statistics updates, particularly in environments with high concurrency and frequent query compilations. This technical demonstration highlights the nuanced interplay between various lock types and priorities, underscoring the sophistication of SQL Server’s concurrency management capabilities.

Imagine a busy intersection in a city, with a constant flow of traffic. In our SQL Server world, this is akin to having multiple queries being compiled at the same time, each placing a schema stability (Sch-S) lock on the statistics metadata object. Now, if this traffic never eases up, our background process tasked with updating statistics can find itself in a bit of a pickle. It’s like a street cleaner waiting endlessly for a chance to clean the road.

In real-world SQL Server scenarios, particularly with intense workloads, this isn’t just a theoretical problem. It can and does happen. Our diligent background process, which is waiting at a low priority due to the ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY setting, might end up waiting far longer than practical. And here’s a crucial point – SQL Server has a finite number of background threads. If one is tied up waiting, it could hog resources that other parts of the database engine might need.

To prevent this from turning into a resource management nightmare, SQL Server has a smart failsafe: a timeout. If our background process has been waiting for several minutes, it’ll give up and move on, ensuring that resources aren’t tied up indefinitely.

But there’s a catch. If the statistics update times out and doesn’t happen, it could leave us with stale statistics. And stale statistics can lead to less-than-ideal query plans. That’s why, by default, the ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY setting is turned off. It’s a balancing act between resource management and query optimization.

For those who love to keep an eye on what’s happening under the hood, SQL Server offers the autostats_update_async_low_priority_status extended event. This tool is like a dashboard camera, capturing when an asynchronous statistics update starts, finishes, times out, or even fails. It’s a great way to monitor and understand the behavior of your SQL Server in high-demand situations.

Lastly, it’s important to note that this configuration behaves a bit differently in replicated environments. On secondary replicas, statistics updates from the primary are automatically applied through transaction log records. However, if you enable the ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY setting on a secondary replica, it will govern the updating of temporary statistics in much the same way as it does for permanent statistics on the primary.

In summary, managing asynchronous statistics updates in SQL Server is a bit like directing traffic in a bustling city. It requires a keen understanding of when to wait, when to move, and how to best keep the data flowing smoothly without causing a jam.

ALTER DATABASE CURRENT SET AUTO_UPDATE_STATISTICS_ASYNC ON;

ALTER DATABASE SCOPED CONFIGURATION SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = OFF;

Step 1

--Create test table and statistics.

DROP TABLE IF EXISTS dbo.stats_wlp;
CREATE TABLE dbo.stats_wlp
(
    c1 varchar(200) NOT NULL CONSTRAINT df_stats_wlp_c1 DEFAULT ('Hello World.')
);

CREATE STATISTICS stats_wlp_stat1 ON dbo.stats_wlp (c1);

--Populate test table with 30000 rows.

INSERT INTO stats_wlp (c1)
SELECT TOP (30000) 'Hello World.' AS c1
FROM sys.all_columns AS ac1
CROSS JOIN sys.all_columns AS ac2;

Step 2

--Start a long-running query. This query places a Sch-S lock on the statistics metadata object to read statistics properties. This simulates a query taking this lock to compile its plan. Note the object_id and stats_id values for the statistics metadata object in the result set. They will be appearing in the following steps.

SELECT t1.c1, s.object_id, s.stats_id
FROM dbo.stats_wlp t1
CROSS JOIN dbo.stats_wlp t2
CROSS JOIN dbo.stats_wlp t3
CROSS JOIN dbo.stats_wlp t4
CROSS JOIN sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.name = 'stats_wlp_stat1';

Step 3

--Using a separate session (query window), observe a granted Sch-S lock on the statistics metadata resource with the same object_id and stats_id, held by the long-running query. Here and below, screenshots show a subset of columns for brevity.

SELECT l.resource_type, l.resource_subtype, l.resource_description, 
l.request_mode, l.request_type, l.request_status, l.request_session_id, 
l.resource_lock_partition, r.blocking_session_id, r.command, r.status, 
r.wait_type
FROM sys.dm_tran_locks AS l
INNER JOIN sys.dm_exec_requests AS r
ON l.request_session_id = r.session_id
WHERE resource_type = 'METADATA'
      AND
      resource_subtype = 'STATS';

Step 4

--Using another separate session (query window), execute a query that will compile a new plan and trigger an asynchronous statistics update due to significant changes in data distribution (recall that we inserted 30000 rows). The query completes quickly because it does not wait for the statistics update to complete.

SELECT COUNT(1) AS cnt
FROM dbo.stats_wlp
WHERE c1 LIKE '%Hello%'
OPTION (RECOMPILE);

Step 5

--Use the query from Step 3 to look at locks again. See that now there is a background process attempting to acquire a Sch-M lock on the same statistics metadata object, with request_status set to CONVERT.

SELECT l.resource_type, l.resource_subtype, l.resource_description, 
l.request_mode, l.request_type, l.request_status, l.request_session_id, 
l.resource_lock_partition, r.blocking_session_id, r.command, r.status, 
r.wait_type
FROM sys.dm_tran_locks AS l
INNER JOIN sys.dm_exec_requests AS r
ON l.request_session_id = r.session_id
WHERE resource_type = 'METADATA'
      AND
      resource_subtype = 'STATS';

Step 6

• Run the query from step 4 again. This time, it is blocked, because it needs to acquire the Sch-S lock on the statistics metadata object to compile its plan, but it gets queued behind the background process waiting for the Sch-M lock.

  SELECT COUNT(1) AS cnt 
  FROM dbo.stats_wlp 
  WHERE c1 LIKE '%Hello%' 
  OPTION (RECOMPILE);

• Cancel the long-running query started in Step 2 to resolve the blocking chain.
• Now let’s enable the configuration to wait at low priority for asynchronous statistics update, to see how it changes this scenario.

  ALTER DATABASE SCOPED CONFIGURATION SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = ON;

• Repeat steps 1-5. In step 5, see that for the background process waiting for Sch-M lock to update statistics asynchronously, request_status is now LOW_PRIORITY_CONVERT, and wait_type is LCK_M_SCH_M_LOW_PRIORITY.
• You can see that the query is no longer blocked and completes quickly, because it is able to acquire the Sch-S lock on the statistics metadata object in front of the background process waiting on a Sch-M lock at low priority. We can continue executing the same query multiple times without any blocking.
• Cancel the long-running query. The query showing statistics metadata locks now returns an empty set, because as soon as we canceled the long-running query, the background process was able to acquire the Sch-M lock, update statistics successfully, and release the lock.

Conclusion

In this post, we’ve explored the dynamics of SQL Server’s lock behavior and the impact of asynchronous statistics updates on query execution. Through practical examples, we demonstrated how to manage potential blocking issues and optimize query performance using SQL Server’s configuration settings. Understanding these interactions is key to efficient database management, enabling SQL Server administrators to enhance performance and maintain smooth operation in their databases. As always, careful testing and observation are recommended when applying any changes in a production environment. Stay tuned for more insights into SQL Server management and optimization techniques.