Anyone can explain how would I benefit from filtered statistics on columns that do not have an index?
Based on [this link], query plans will differ as the optimizer will estimate more accurately. I see the differences in plans when I run under the AdventureWorks sample DB.
But I still do not see the benefit as the IO logical reads are still the same with and without filtered statistics.: http://msdn.microsoft.com/en-us/library/ms190397.aspx#CreateStatistics
If you have queries with complex but predictable where clauses which include multiple columns then creating filtered statistics might make sense. They are cheaper than indexes as far as the system resources are concerned, but still provide information to the engine about so-called cardinality estimates (a fancy word for selectivity). The information is stored for each prefix column of the statistics, meaning that if the queries fail to include the prefix column in the predicates then the statistics are useless, just like the indexes are useless when the first column of the index is not included.
I am sure that someone will have a better answer, but these are my 2 cents.Oleg
answered Oct 27, 2010 at 03:17 PM
I cannot seem to get this example to work as I wanted. Maybe it can be improved upon - it is just too late for me today.
I play in my test DB SandBox, that it all you will need to change to get this to work.
I clear the cache in this script too - DO NOT PLAY WITH THIS ON A PRODUCTION SYSTEM!!!!!!
ALTER DATABASE SandBox SET AUTO_CREATE_STATISTICS ON, AUTO_UPDATE_STATISTICS ON
answered Oct 27, 2010 at 03:42 PM
Thanks @WillianD. Seems that filtered stats are still not helping the optimizer. I will do some additional research about this. The
answered Oct 27, 2010 at 05:52 PM