Press "Enter" to skip to content

Filtered Statistics and Table Performance

Guy Glantser provides a use case for filtered statistics:

Let’s say you have a very large table on a SQL Server 2012 Standard Edition instance. This means: old cardinality estimator and no partitioning. The table has a DATETIME column, which is ever-increasing, and it contains 5 years of data (it has to, due to regulations). The auto-update statistics kicks in only every 4 days, more or less, even when trace flag 2371 is enabled. The problem is that users usually query the table for the last day, and only rarely need to access older data. Since auto-update statistics uses a very small sample rate for very large tables, the result is not very accurate. The bottom line of all this is that most of the time you get a poor execution plan, because the optimizer estimates very few rows, while in fact there are many rows. What can you do?

I’m not sure I’ve ever used filtered statistics but it is good to know such a thing exists.