1

We've noticed on one of our servers, that after some time queries are falling to full table scan, seems due the outdated statistics. SQL Server is quite busy, receiving hundreds of queries/row modifications per minute, can it be a reason, why statistics are not updated?

We noticed, that if we stop the app, statistics get updated, hence wondering if there might be a case that SQL Server does not find a window or something to update statistics, or that is not possible and there's another reason hiding?

Manually triggering statistics update works, but I would like to avoid scheduling periodic statistics update if possible, as I did not find information that statistics update would be prevented because of stream of selects/inserts/updates.

Update: Auto statistics update is enabled

2
  • 1
    Do you have your auto update on stats on? SELECT d.is_auto_update_stats_on, d.is_auto_update_stats_async_on FROM sys.databases AS d;
    – Peter
    Mar 9, 2023 at 8:15
  • 1
    And what is your SQL Server version? The trigger to update stats can be different depending on the SQL Server version.
    – Peter
    Mar 9, 2023 at 8:17

1 Answer 1

3

Two things on this. Automatic statistics updates are based on changes to the data. While you may think your system is very active, it may not be active enough to trigger the updates. Prior to SQL Server 2016, the default behavior required that 20% of data be modified (INSERT, UPDATE, DELETE, some combination thereof) before stats updates occurred. Unless you set TRACEFLAG 2371, then it used a sliding scale to determine updates. 2016 & later, the default is the sliding scale. Documentation is very thorough on this.

The other thing to know is that, yeah, statistics updates are in fact, subject to contention. So if there are inserts, updates, whatever, in the way, holding locks, statistics updates will wait. You can experiment with enabling AUTO_UPDATE_STATISTICS_ASYNC as a way to shift when statistics updates occur, but there will still be contention (documented in the link above). If you're in Azure SQL Database, Managed Instance, or 2022, you can also look to ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY as a way to adjust contention (although, seems like you don't want to see a longer wait on stats updates, so this may not be a good choice, testing is key).

And yes, the automatic statistics maintenance may not be enough in some circumstances. That's why you can take direct control through UPDATE STATISTICS. I'd add that to the testing to see if taking direct control over some tables, or some indexes, makes a difference. It has for me in the past. Hope that helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.