Blitz Result: Auto Update Stats Async Enabled
SQL Server maintains statistics that help it decide how to build better execution plans. It periodically reviews the content of tables, updates its statistics about the distribution of data in fields, and uses that for execution plans. By default, this happens automatically – statistics are updated when about 20% of the data in a table changes – and it happens whenever a query needs to read data from that table. This part of our SQL Server sp_Blitz script checks sys.databases looking for databases where auto-update-stats-async has been turned on, meaning SQL Server will go ahead and build an execution plan with the out-of-date statistics and then update statistics in the background to help the next query’s performance.
This setting is up for debate in the SQL Server community. We recommend setting it back to the default (disabled) unless you’ve got evidence that delays caused by statistics updates are causing you problems. There are opposing viewpoints, though, and here’s a few links for more learning:
- Tara Kizer’s opinion on auto_update_stats_async
- Microsoft KB post: memory leak if you enable auto_update_statistics_async
- Erik Darling thinks everyone is wrong
To Fix the Problem
In SQL Server Management Studio, you can right-click on each database and go into its properties to change Auto Update Stats Asynchronously back to the default, Disabled. Auto Update Stats also has to be enabled for this change to actually do anything – otherwise stats aren’t being updated anyway.
After the change, you can monitor overall system performance looking for improvements, especially around query execution plans.