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 

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

Key Lookup Vs Nonclustered Index Seek (without Key Lookup)

Before we start lets understand, what we mean with these two words:

Key Lookup Operator: When your nonclustered index doesn’t cover all the data required by the select query it jumps to cluster index to get the data.

Non Clustered Index Seek (without Key Lookup): When your non clustered index cover all the data required by select query then the operator which have greater chance to come in execution plan is Non Clustered Index(without Key lookup)

This reflect that Key Lookup is a very expensive operation compare to NC Index Seek(without key lookup) because it perform a IO to clustered index the from clustered to heap to get the data which is not covered by nonclutered index.

Let’s stimulate a situation where we get lookup operator

CREATE TABLE IndexSeek_KeyLookup_Demo_TB

(

    id   INT  IDENTITY (1, 1),

    col2 DATETIME        ,

    col3 NUMERIC (28, 10) NOT NULL

);

Command(s) completed successfully.

Our table is ready to get some data into it. Here we are trying to insert 5 Lkh data where date column i.e. Col2 has value 16June2014 and 5 record with col2 value 16June2014 values.

DECLARE @i AS INT = 0;

BEGIN TRANSACTION;

WHILE @i < 500000   

    BEGIN

        INSERT INTO IndexSeek_KeyLookup_Demo_TB (col2, col3)

        SELECT '20140616',

               1000 * rand();

        SET @i = @i + 1;

    END

SET @i = 0;

WHILE @i < 5

    BEGIN

        INSERT INTO IndexSeek_KeyLookup_Demo_TB (col2, col3)

        SELECT '20140617',

               1000 * rand();

        SET @i = @i + 1;

    END

COMMIT TRANSACTION;

It’s time to create clustered index in column on id field.

create clustered index ix_IndexSeek_KeyLookup_Demo_TB_id

on IndexSeek_KeyLookup_Demo_TB(id)

Now since cluster index is in place, lets create non cluster index on column col2

create nonclustered index [ix_col2]

on IndexSeek_KeyLookup_Demo_TB ([col2] asc)

Command(s) completed successfully.

Now lets create covering nonclustered index on the same column

create nonclustered index [ix_col2_2]

on IndexSeek_KeyLookup_Demo_TB ([col2] asc) include (col3)

Command(s) completed successfully.

Now lets fire both the query in batch to see cost variance between two operator.

SELECT SUM(col3) FROM IndexSeek_KeyLookup_Demo_TB WHERE col2='20140617'

SELECT SUM(col3) FROM IndexSeek_KeyLookup_Demo_TB with (index=[ix_col2_2]) WHERE col2='20140617'

Go

So here we saw Index seek without Key look take only 17% of total cost of batch where as Key Lookup take 83% of total cost of batch, here we can say use of covering index drastically improve the performance of query.

I started because I find u interesting, I stand up with all versions of urs and all of sudden I came to knw… It was just me n you J

Post Reference: Vikram Aristocratic Elfin Share