10 January 2011
Anyone who has ever put a filtered index on a table knows that auto-update-stats is inadequate at keeping the query optimizer informed about them. The reason is that to fire a stats update, ~20% of the table has to change. If you have a table with 500 million rows, and a filtered index covering 5 million of those rows, you’d still need ~100 million rows to change before stats would be automatically updated.
Luckily we already have a script that will use advanced criteria to update statistics for us. I refactored the original script and test to account for filtered indexes. Now, if we want to update stats if 15% of the table changes, we will also update stats if 15% of the filtered index changes.
Looking at our original example, the big table with 500 million rows would get its stats updated if 750,000 rows inside the filtered index are changed. That’s much better.
The key is that the sys.partitions view includes the estimated number of rows in an index. If an index is filtered, it will have a lower number of rows. The ratio of rows in the index to rows in the table tells you roughly what percentage of the table the index covers.
Script: Uber-Update-Stats-v11.sql Unit Test: Uber-Update-Stats-Test-v11.sql