time for stats to get updated when auto update statistics async = true

Question

time for stats to get updated when auto update statistics async = true

robh0502

SSC Enthusiast

Points: 199
More actions
October 31, 2023 at 1:32 am

#4317935
hi,
I'm using the following database version of MS SQL SERVER
Microsoft SQL Server 2022 (RTM-GDR) (KB5029379) - 16.0.1105.1 (X64)
I was experimenting with the Auto Update Statistics Async = TRUE in my test database and noticed that the time it took for my table column stats to get updated took anywhere from immediate to about a minute. Just wondering if anyone could explain the mechanism of how this feature works. My understanding is that table column stats would get updated if the modification count is greater than the threshold and after the query is executed once. Here's some test code.
```
create table testtab
(col1 int)

DECLARE @count INT;
SET @count = 1;
WHILE @count<= 100
BEGIN
   INSERT INTO testtab VALUES(1)
   SET @count = @count + 1;
END;

select * from testtab where col1 = 1 --run query to create stats object

--generate enough modifications
DECLARE @count INT;
SET @count = 1;
WHILE @count<= 600
BEGIN
   INSERT INTO testtab VALUES(1)
   SET @count = @count + 1;
END;

select * from testtab where col1 = 1 --run query again to trigger async stats update

--however, after a few test runs, stats update takes anywhere from immediate to 1 minute
```

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

You've got the basics mostly right. Async updates will occur after, not query execution, but after the compile process is complete. This is why, you may still see almost instantaneous updates to statistics occurring, because they compile process was very short. You can validate this behavior by capturing extended events and using causility tracking to see the order of operations between the execution of a query and when the stats get updated.

The core principal here is simple, instead of immediately causing blocks when a stats update needs to occur, before the query gets compiled (or recompiled), you're choosing to let the plan get created with old stats. Then, the stats get updated immediately following the compile process completing. It's not a night and day behavioral difference. It just delays stats updates. This may help some queries that are unlikely to see massive plan changes as the statistics change. However, other queries could suffer by getting plans that aren't based on more up to date queries. Generally, unless I've already established an issue with blocking due to stats updates (again, extended events are your friend), I leave the async update disabled.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

robh0502 SSC Enthusiast Points: 199 More actions · Answer 2

hi Grant,

thanks for the info. I'm brand new in SQL Server, so I appreciate your answers to my questions. I read an article on extended events. However, would you be able to provide me with some guidance regarding how to setup the event?

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Here's an example. Probably best to trim some of the events away, and obviously you'll want a different set of filter criteria.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning