SQL Server 2019: WAIT_ON_SYNC_STATISTICS_REFRESH

Well Have You Ever

I mean ever really wondered just how long a query of yours waited on stats to automatically update before running?

Have you ever been absolutely, positively sure that those gosh dang darn stats updates were putting your query on the trolley to Slowsville?

Your wishes will be 100% granted in SQL Server 2019.

Now, this wait doesn’t show up when stats are created the first time.

So if you run a query with a where clause on a column that doesn’t already have stats, the initial gather won’t show.

This is only for updates. Not creates.

Got it?

In Action

The following script will take the Votes table, and make a copy of it called Vetos.

Then it’ll create a PK/CX (for some reason?), and run a query against a couple columns that are mostly NULL.

Voting data gets cleaned out of the SO data dump.

The last query is important because it generates the initial stats on both of those columns.

Now let’s put some work into it!

This table has 52,928,720 rows in it. Not the biggest, but a decent size to maybe have to wait on stats to update.

Ready Steady

In separate windows, I’ll run these:

They’ll trigger the stats refresh.

Fun. Yes.

Checking in on each session’s wait stats using dm_exec_session_wait_stats, our wild wait appears.

I thought you were dead.

So there you have it. 52 million row stats refreshes take about half a second.

That wasn’t very exciting. Let’s try something else.

Tricks, Kids

If we start from scratch, but instead of letting SQL Server create stats automatically by running a query, let’s create statistics with some funny options, and then update the columns.

Now, when we run our select queries, things really slow down.

Sunny

Quite a bit longer on those.

Actionable?

What would one do if they encountered 15-16 waits on this in real life?

Well, you have some options.

  • Update stats asynchronously
  • Create stats with no recompute and handle stats updates yourself
  • Update statistics more often than you currently do, trying to stay ahead of automatic updates

It’s hard to see this being a really big issue outside of very large tables, and perhaps only on under-powered servers.

Or if someone created statistics with some rather impolite settings.

Thanks for reading!

2 thoughts on “SQL Server 2019: WAIT_ON_SYNC_STATISTICS_REFRESH”

  1. Note that SentryOne Plan Explorer – on the desktop – will show any StatMan calls (including duration) when collecting an actual plan against any version. Now it can also show this new wait type on the wait stats tab, when the query has run long enough for waits to also have been collected.

Leave a Reply

Your email address will not be published. Required fields are marked *