SQL SERVER – Resolve WAIT_ON_SYNC_STATISTICS_REFRESH Wait Type

Every new consulting engagement teaches me new learnings. In the recent SQL Server Comprehensive Database Performance Health Check engagement, I had faced a very interesting issue, which I had not faced so far ever in my career before. I recently faced a brand new wait type WAIT_ON_SYNC_STATISTICS_REFRESH. Let us learn how we can resolve it.

Brief Story of Statistics

Statistics are one of the most important elements in SQL Server. They decide the optimal execution plan for your SQL Server and that is why it is a good idea to keep the statistics updated with the latest data. Here is the script which can tell you how old your statistics are:  How to Find Outdated Statistics?

SQL Server updates automatically the statistics in the following two conditions in most of the case:

1. If you have left auto-update or auto-create statistics on, you should not worry at all, SQL Server will make the task itself.
2. If you have left auto-update or auto-create statistics off, you should manually update the statistics when it reaches the default of 500+ 10% of table rows (in SQL Server 2017/2019) and 500+ 20% of table rows (in the previous version of SQL Server).

This works out great in most of the cases and leads to better performance for your queries.

However, there are edge scenarios and special cases when the same statistics update can actually slow down, hence quite a lot of people turn off these settings and update them manually in the night (or when the server is not busy). Now, this leads to another problem when your data is updated during the day and SQL Server still uses the older statistics which was updated during the previous night.

One-Line Summary: After working in the area of SQL Server Performance Tuning for over 10+ years, I have come to the conclusion that it is best to keep the auto-update and auto-create statistics, while you can still update your statistics manually during the night.

Cause of WAIT_ON_SYNC_STATISTICS_REFRESH

Now the question is when does WAIT_ON_SYNC_STATISTICS_REFRESH show up. When you have kept your auto-update or auto-create statistics on, and statistics are stale, here is the small algorithm which SQL Server engine follows.

Step 1: Query is executed
Step 2: SQL Server engine decides if the statistics are updated or stale.
Step 2a: If statistics are not updated, halt the query execution and update the statistics.
Step 3: If statistics are updated, SQL Server looks for execution in the cache, if the execution plan found in the cache, it uses the one or rebuilds a new one based on the available statistics.
Step 4: Query execution

Now you can see how query execution works. If the statistics are updated the step 2a is not needed. However, if due to any reason, statistics need to be updated, step 2a might introduce a significant delay in your query execution for the first time and after that in the subsequent runs, the queries will run fine.

If your table is huge or your server is busy, the Step 2a can really give you a slow performance to your queries. Here is a very simple solution to this problem.

Enable AUTO_UPDATE_STATISTICS_ASYNC

The reason Step 2a is adding delay because the way SQL Server updates the statistics is synchronous. You can easily change that to synchronous by changing the following settings at the database level in either SSMS or via T-SQL.

T-SQL

USE [master]
GO
ALTER DATABASE [YourDBName]
SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT
GO

SSMS – SQL Server Management Studio

Here is the screen of the database property where you can change the settings for the synchronous update of the statistics.

Once we changed this setting the issue which my clients were facing about WAIT_ON_SYNC_STATISTICS_REFRESH immediately disappeared and the queries started to run amazingly fast. Every day I learn something new about SQL Server while working on Comprehensive Database Performance Health Check engagement. You can follow me on twitter here.

Reference: Pinal Dave (https://blog.sqlauthority.com)