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.