Should auto update statistics be turned off, and instead running nightly statistics update job to improve performance?
Well let’s discuss on how to approach this scenario and the options available.
First step would be to establish the context and narrative around this recommendation, for why someone would want the auto statistics update turned off. Therefore, refer to the below questions:
- What issue or bottleneck are you experiencing for the auto statistics gathering to be switched off – is it CPU or disk i/o issue? Has there been an issue?
- What trace flags are in place – is the 2371 trace in place?
- Is there a nightly index and statistics gathering job already in place and working?
- Any reason why auto update stats asynchronously can’t be enabled?
- What is the downside to not collecting stats during the day – stale stats on volatile tables that could impact performance?
Note: Statistics updates can be either synchronous (the default) or asynchronous.
Generally, my recommendation would be to leave synchronous option enabled although we have some options below available;
- The auto statistics asynchronously could be enabled. This would be beneficial if you frequently execute the same query, similar queries, or similar cached query plans. Your query response times might be more predictable with asynchronous statistics updates than with synchronous statistics updates because the query optimizer can execute incoming queries without waiting for up-to-date statistics. Although queries that compile after the asynchronous updates have completed those will benefit from using the updated statistics. See considerations for enabling asynchronously on the following blog post: https://mostafaelmasry.com/2015/09/18/difference-between-auto-update-statistics-and-auto-update-statistics-asynchronously/
- Trace flag 2371 (Available in In SQL Server 2008 R2 SP1 onwards and has been turned on by default in SQL 2016 (with compatibility 130).) – Enabling this trace flag would update statistics into a dynamic percentage rate. The higher the number of rows in a table, the lower the threshold will become to trigger an update of the statistics. As a result, the threshold to trigger update statistics will be calculated based on the number of rows in the table and generally would lead to more up-to-date statistics for larger tables. As default 20% fixed threshold for update statistics is used when trace flag 2371 hasn’t been enabled.
- Could provision maintenance scripts that includes an update statistics job. This could compliment any auto-stats strategy
However, if auto statistics was disabled, then stale statistics could be used leading to potentially bad execution plans causing issues for query performance. As up to date accurate statistics help the optimizer to prepare better plans. Alternatively, if you are seeing bottlenecks e.g. CPU utilization or disk I/O during busy times then you could disable the auto statistics and implement update statistics maintenance script process. However, there is no guarantee that this will address the root cause of any bottleneck and could degrade performance.
Please share your thoughts and ideas on this topic.