patternsqlMinor
Auto Update Statistics in SQL Server 2008R2: Why do some statistics remain stale despite a large number of row inserts?
Viewed 0 times
whynumberupdatedespiteautosqlinserts2008r2statisticsremain
Problem
During investigation of a slow query it appeared that the execution plan was exceptionally suboptimal (A nested loop performing 9 million executions of a seek where the estimated number of executions was 1). Having confirmed that some relevant statistics where indeed out-of-date I rebuilt the stats and the performance problem is effectively resolved.
This database has Auto Update Statistics enabled (on by default). I understand there is a threshold for auto stats updates based on there being 20% + 500 row modifications (update/insert/deletes). This threshold appears to have been exceeded by a large degree on multiple indexes, as such it appears there is either (A) a problem with auto updates or (B) There is more to the update strategy than I have been able to find in online documentation.
I appreciate that a scheduled task can be set up to update stats and this is likely to be the approach that we take if no other solution can be found, but it does leave us confused as to why such large numbers of modifications would not trigger an auto update for some stats - understanding why might help us to decide which stats need to be updated by a scheduled task.
Some additional notes:
-
The problem was noted in a database where the data is being created by a load test and as such a large quantity of data is being added in a short space of time, thus if the auto update occurred periodically (e.g. once a day at most) then this may explain some of the observed behavior. Also our load tests tend to stress the database heavily, therefore I wonder if SQL is deferring stats updates while there is heavy load (and subsequently not updating the stats for some reason).
-
In trying to recreate this issue with a test script containing successive INSERT, SELECT and DELETE statements the problem to did not occur. I'm wondering if the distinction here is that these statements each affect many rows per SQL statement, whereas our load test script will tend to insert rows individually.
This database has Auto Update Statistics enabled (on by default). I understand there is a threshold for auto stats updates based on there being 20% + 500 row modifications (update/insert/deletes). This threshold appears to have been exceeded by a large degree on multiple indexes, as such it appears there is either (A) a problem with auto updates or (B) There is more to the update strategy than I have been able to find in online documentation.
I appreciate that a scheduled task can be set up to update stats and this is likely to be the approach that we take if no other solution can be found, but it does leave us confused as to why such large numbers of modifications would not trigger an auto update for some stats - understanding why might help us to decide which stats need to be updated by a scheduled task.
Some additional notes:
-
The problem was noted in a database where the data is being created by a load test and as such a large quantity of data is being added in a short space of time, thus if the auto update occurred periodically (e.g. once a day at most) then this may explain some of the observed behavior. Also our load tests tend to stress the database heavily, therefore I wonder if SQL is deferring stats updates while there is heavy load (and subsequently not updating the stats for some reason).
-
In trying to recreate this issue with a test script containing successive INSERT, SELECT and DELETE statements the problem to did not occur. I'm wondering if the distinction here is that these statements each affect many rows per SQL statement, whereas our load test script will tend to insert rows individually.
Solution
Some info, if not a definitive answer
It's been blogged recently
There is a whitepaper too. See the section "Maintaining Statistics in SQL Server 2008" where there are some conditions that sound like affect you. Example:
One limitation of the automatic update logic is that it tracks changes to columns in the statistics, but not changes to columns in the predicate. If there are many changes to the columns used in predicates of filtered statistics, consider using manual updates to keep up with the changes.
At the end there are some settings to check too: what if OFF at the DB level which overrides an ON at the index/stat level?
HTH...
It's been blogged recently
- When are Statistics Updated – What triggers Statistics to Update
- Comment from Paul Randal
There is a whitepaper too. See the section "Maintaining Statistics in SQL Server 2008" where there are some conditions that sound like affect you. Example:
One limitation of the automatic update logic is that it tracks changes to columns in the statistics, but not changes to columns in the predicate. If there are many changes to the columns used in predicates of filtered statistics, consider using manual updates to keep up with the changes.
At the end there are some settings to check too: what if OFF at the DB level which overrides an ON at the index/stat level?
HTH...
Context
StackExchange Database Administrators Q#9817, answer score: 8
Revisions (0)
No revisions yet.