Slow Storage Can Cause Slow Compilation Time in SQL Server

Up till now, I’ve thought of compilation time in SQL Server as being dependent only on CPU resources– not something that requires fast storage to be speedy. But that’s not quite right.

Slow storage can result in periodic long compile time in SQL Server. And long compile time not only extends the runtime for the query, it can also result in blocking with waits for compile locks.

Thanks to Erik Darling for helping me figure this out, and explaining this all in his video, What Else Happens When Queries Try To Compile In SQL Server: COMPILE LOCKS!. For great details and demos, go watch that! I’ll be working through the topic with some simple flow charts here.

Synchronous auto-update of statistics and compilation time

Let’s unpack this.

Statisics

• Statistics are objects that store information describing the distribution of data in a column or index. Statistics help the query optimizer identify efficient ways to execute a query by providing row estimates, which influences the selection of indexes and physical join types.
• Databases in SQL Server default to automatically refreshing statistics when the data in a table changes significantly. This is great, it helps the query optimizer have a better chance at creating fast execution plans without human intervention.

There are two modes for auto-update of statistics in SQL Server

1. Synchronous stats updates (default): The query that triggers the statistics update waits for the update to complete before executing. This ensures the query uses the most up-to-date statistics but can cause a delay in execution. The most common misunderstanding here is that the query triggering statistics update will be a query that modifies the data. In fact, it is a query that needs to read from the table and use the statistics in optimization that triggers the auto update of statistics.

2. Asynchronous Stats Updates: The query proceeds without waiting for the statistics to be updated. The stats are updated in the background

   1. This avoids delays for the query which triggers the automatic statistics update, but may result in the query using outdated statistics. This may increase the chances of that query getting an inefficient query plan.

Here’s what I didn’t realize

I had previously thought that under the synchronous mode of auto-update statistics, queries would wait to start compiling until the statistics were updated. In fact, that period of time waiting for statistics to update is counted as part of compilation time. (You can see this in Erik’s demo.) There is a wait statistic, WAIT_ON_SYNC_STATISTICS_REFRESH, which will occur, which…

Occurs when waiting for synchronous statistics update to complete before query compilation and execution can resume.

Source: sys.dm_os_wait_stats docs

That description does say that compilation is effectively paused / has started when this wait happens. However, this wait is not surfaced in actual execution plans.

It is also not tracked in Query Store:

Query Store tracks wait stats only during query execution, not during query compilation. This restricts Query Store’s ability to track compilation wait stats.

Source: sys.query_store_wait_stats

In other words, long compilation times that you see in query store may or may not be long because of a wait on synchronous stats update OR a wait on compilation locks. You can’t tell from just query store. (Reminder: if your queries have a timeout while still in the compilation phase, they will NOT be recorded in query store for that execution at all.)

Compile lock waits and blocking

When you have slow storage and synchronous stats auto-updates, you are more likely to have long compilation times (because of what we described above), and you are therefore more likely to see compilation lock waits. This is especially true in an OLTP workload where some queries are run very frequently from multiple sessions, and are likely to run concurrently.

Compile locks in SQL Server prevent changes to the plan cache during query compilation to ensure consistency. If we run the exact same procedure or parameterized query from multiple sessions, and one is having a long compile time, the others wait until it has compiled and cached the plan. They can then pull this plan from the cache and off they go.

This is great when compilation is fast. But when compilation time starts taking 20 seconds, 30 seconds, or longer, this can become quite disruptive.

How do you fix this?

I think the easiest thing to monitor for is the presence of WAIT_ON_SYNC_STATISTICS_REFRESH waits. If you don’t have those waits, long compilation time is not related to waiting on synchronous stats updates.

If you do see those waits, look for blocking on compilation locks. This can help you identify which queries are impacted by this. If it’s only a few queries, you may want to work at simplifying those queries.

If it’s not just a small set of queries, consider changing your database setting and using asynchronous statistics updates on the database. There’s a few disclaimers when it comes to async stats updates. As mentioned above, this setting may result in the query which triggers automatic stats update using outdated statistics. This may increase the chances of that query getting an inefficient query plan.

But additionally, Microsoft warns:

Asynchronous statistics update is performed by a background request. When the request is ready to write updated statistics to the database, it attempts to acquire a schema modification lock on the statistics metadata object. If a different session is already holding a lock on the same object, asynchronous statistics update is blocked until the schema modification lock can be acquired. Similarly, sessions that need to acquire a schema stability (Sch-S) lock on the statistics metadata object to compile a query can be blocked by the asynchronous statistics update background session, which is already holding or waiting to acquire the schema modification lock. Therefore, for workloads with very frequent query compilations and frequent statistics updates, using asynchronous statistics can increase the likelihood of concurrency issues due to lock blocking.

Source: Statistics docs

There is a setting that can help with this if you are running SQL Server 2002, Azure SQL Database, or Azure SQL Managed Instance:

In Azure SQL Database, Azure SQL Managed Instance, and beginning in SQL Server 2022 (16.x), you can avoid potential concurrency issues using asynchronous statistics update if you enable the ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY database-scoped configuration. With this configuration enabled, the background request will wait to acquire the schema modification (Sch-M) lock and persist the updated statistics on a separate low-priority queue, allowing other requests to continue compiling queries with existing statistics. Once no other session is holding a lock on the statistics metadata object, the background request will acquire its schema modification lock and update statistics.

Source: Statistics docs

There are more details about this setting in Dimitri Furman’s blog post, Improving concurrency of asynchronous statistics update.

So consider testing those settings together if you’ve got the option.