In what circumstances I am more likely to benefit from auto update stats async on?

Question

I update statistics on a weekly basis using Ola Hallengren's solution.

Based on the article below, I am considering enabling the Auto Stats Async and turning on trace flag 2371.

Databases With Large Tables Should Use Auto Update Stats Async Feature

Right now, the async option is false for my database:

Also, from Important Hotfix for SQL Server 2008 and Newer:

This setting reduces unpredictable query performance by allowing statistics to be automatically updated asynchronously while your currently running queries continue to use the old statistics until the updated statistics are ready to use. The alternative to this (which is the default) is for query execution to halt (only for queries that use statistics for that object) while the statistics are automatically updated synchronously for that object. Depending on the size of the object, and on your hardware and I/O subsystem, this could take anywhere from a few seconds to many minutes.

Question:

In a test environment, what could be a good and simple test to do before/after setting Auto Update Stats Async to ON?

In what circumstances I am more likely to benefit from auto update stats async on?

Databases 500GB+, big tables.

Answer 1

I think you will be much better off testing your actual workload under sync and async, and see if you observe any differences. Coming up with a sweet spot to suit a particular feature is kind of backwards.

Generally, async is primarily beneficial when you don't care if the query that triggered the stats update still runs that last time with the old stats (or users can't wait long enough for the stats update to happen and the query to run). The threshold will not be easy for anyone else to tell you - it depends on how long the stats update takes (so depends on data size, I/O subsystem, sample size, etc.), how much the stats update helps the query (it may or may not generate a different plan, for example), and we don't know your end users' definition of "this query is taking too long."

Again, generally, sync is beneficial when you know that stats go south quickly, and that any query that runs with stale statistics will suffer. In this case, you should have a good idea that the duration of the stats update + the time of the improved query is on average less than the duration of the query using the stale stats.