Work Around for Filtered Index Parameter Sniffing Problem

Problem Statement: The problem with Filtered index is, when a local variable is passed as a parameter to the query, Query Optimizer does not select the correct Index (Filtered index) even though the WHERE  condition (the selectivity) falls in the range of Filtered index.

Again I am using AdventureWork2008R2 database for explanation. We will make use of Person.Person table.

We are interested to fire this query

select PersonType  from Person.Person where PersonType = 'SP'

Here I am creating 2 indexes on the PersonType column, one Non Custered Index and another Filtered Index

create index ix_Person_PersonType

on Person.Person(PersonType)

Command(s) completed successfully.

create index fix_Person_PersonType

on Person.Person(PersonType) where PersonType = 'SP'

Command(s) completed successfully.

Now our indexes are in place, lets fire query to see the execution plan.

We are firing the query against Person table for PersonType like ‘SP’, and expecting Optimizer to pick up newly created Filtered Index.

select PersonType  from Person.Person where PersonType = 'SP'

Thats fantastic it picked up the right index what we were expecting. Here in the above query we have explicitly passed the value in the where predicate that is PersonType = ‘SP’

Let see what Optimizer will do if we pass the value through local variable.

declare @para varchar(2) = 'SP'

select PersonType  from Person.Person where PersonType = @para

Optimizer did not use the Filtered index even though the where predicate search the same records.

The reason behind is Optimizer don’t have idea what value the local variable will hold at compile time, so it trade off Filtered Index and choose the Non clustered index.

Workaround Solution: Option(RECOMPILE) we can use the statement level recompile statement to tell the engine to recompile the plan at execution time.

declare @para varchar(2) = 'SP'

select PersonType  from Person.Person where PersonType = @para

Here in this query we can see the @para value is not available at compile time only at run time it is exposed. So the alternative solution would be recompiling the  plan at run time with @para value is available.

declare @para varchar(2) = 'SP'

select PersonType  from Person.Person  where PersonType = @para option(recompile)

So the execution pan clearly tells that when you use execution level RECOMPILE option the correct index will be picked up. Still try to avoid statement level recompilation and make use of stored cache plan. This is just an work around solution.

If your code has bliss then do you think you need to go out in search of materialized happiness?? J

Post Reference: Vikram Aristocratic Elfin Share