3

In a SQL Server 2012 SP1 instance, I have a filtered index on the PK column of a table that looks like this:

CREATE INDEX [RF_IXF_Orders_OrderNumber] ON [dbo].[Orders]
(
    [OrderNumber] ASC
)
WHERE [OrderSource]='MO'
AND [Cancelled]=(0)
AND [NumItems]>(0)
AND [OrderDate]>'2014-05-15';

The Row Count (dm_db_partition_stats.row_count) of the index is 8416, the Rows Changed (sysindexes.rowmodctr) is 16803 (193.6%), and Auto Update Statistics is True. There have been 8400 user_scans and 50,088 user_updates. The last user_scan was today, yet the statistics haven't been updated in about a week.

Why wouldn't the statistics have auto updated?

6

Fatal flaw in filtered indexes/statistics: churn is measured by % of row count for the table, not the index/statistic. Paraphrased from my blog post here:

Filtered indexes don't benefit from auto-updating of stats based on a percentage change of the subset of the table that is identified by the filter predicate; it is based (like all non-filtered indexes) on churn against the whole table. This means that, depending on what percentage of the table is in the filtered index, the number of rows in the index could quadruple or halve and the statistics won't update unless you do so manually. Kimberly Tripp has given some great information about this (and Gail Shaw cites an example where it took 257,000 updates before statistics were updated for a filtered index that contained only 10,000 rows):

http://www.sqlskills.com/blogs/kimberly/filtered-indexes-and-filtered-stats-might-become-seriously-out-of-date/

Also, Joe Sack has filed a Connect item that suggests correcting this behavior for both filtered indexes and filtered statistics:

http://connect.microsoft.com/SQLServer/feedback/details/509638

You shouldn't rely on auto-update statistics for filtered objects. If the cardinality is important and the subset changes a lot more than the whole table, you will need to manage these manually.

1
  • 1
    Aaron, thanks again for your insight. I up-voted the Connect item and added your comments to it (with attribution, of course). I'll add a step to one of our maintenance jobs to update the stats on all the filtered indexes. Mar 5 '15 at 2:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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