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!