After writing a post on SQL Server’s new parameter sniffing option and how it helps performance of parameterized wildcard queries, I started to wonder if we can get similar benefits with filtered indexes.

Filtered indexes come with the gotcha of requiring that the predicates of the index be hardcoded (non parameterized).  I can see the rationale for this given you can’t cache a plan that uses a filtered index if the input parameter isn’t in the filtered index.

I’ll cut straight to the point. It doesn’t help!

I like to spend the time documenting the setup of my tests so I can minimize the amount of follow up questions regarding the validity of my test results.  I spent a decent amount of time messing with this unfortunately to come to this conclusion.  I won’t bore you with the details of this, but if anyone has seen different results I’d be really interesting in hearing from you in the comments.

I usually start a blog post proving out a concept I know works.  Sometimes I will experiment and see how some new features behave.  In this case pretty early on I found it doesn’t help. Sorry!