Auto Update Statistics not triggering on filtered indexes
-
I've got a table with 186,703,969 rows, about 300GB of data. There are several filtered indexes on the table, two among them filter on "Specific_ID IS NOT NULL" - top row in the screen shot, and "Column5 = 'Sum Available' " in row two.
I've found that for two of the Index Statistics, Auto Update Stats has not triggered, even though the percentage change is high, in one case over 100%, as you can see in the screen shot of the statistics for the two indexes. Note, the filter returns only 187,754 rows for the one index, so a very small percent of the total table rows.
Is this expected behaviour or a feature?
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated. -
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
Keep in mind, the modif % for filtered indexes is calculated based on the full tables number of rows !!
If needed, schedule a sqlagent job to update ( the wanted ) statistics.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data and code to get the best help- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
-
I'm aware that Update Stats is triggered at the table level, but I was hoping someone knew of a system based fix. This seems a weakness in the update stats system given that index changes can be very different from table changes which could result in index stats being stale a long time before the table changes trigger an auto update stats.
I thought of creating a job to analyze the index rows modified counts and trigger an update stats at that level, but wanted to see if other's are facing this sort of problem. It's not something I've looked into before, at least not at this level of detail.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply