Parameter Sniffing with Filtered Index

Filtered Index is one of the fantastic advancement in the indexing arena, but with each great feature there is always a trade off, which we called it as an exception. 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.

Let’s take an example to clarify our point. Here 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'

Now let me check Is there any index build up for PersonType column.

select name from sys.indexes where object_id = OBJECT_ID('Person.Person')

AK_Person_rowguid

PXML_Person_AddContact

PXML_Person_Demographics

XMLPATH_Person_Demographics

XMLPROPERTY_Person_Demographics

XMLVALUE_Person_Demographics

Ah! There is no index created on PersonType column, why wait lets create it

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.

Lets fire the query to see our indexes created

select name from sys.indexes where object_id = OBJECT_ID('Person.Person')

AK_Person_rowguid

ix_Person_PersonType

fix_Person_PersonType

PXML_Person_AddContact

PXML_Person_Demographics

XMLPATH_Person_Demographics

XMLPROPERTY_Person_Demographics

XMLVALUE_Person_Demographics

Now our indexes are in place, its time to fire query to see whether optimizer is picking up correct index.

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.

Conclusion: If your query hold local variable as a parameter passed, then try to avoid creating Filtered index on the same.

If something very precious to you is parting u, no need to pain up, there must be some good reason, don’t be sad, best moment yet to come, still waiting for u at your door step, knocking; listen the beauty of her knock, Celebrate happiness my friend. Here it goes happiness of completing 50^th article of the year, thank you my friend   :)

Post Reference: Vikram Aristocratic Elfin Share