Filtered index is not used when variable in WHERE condition

Question

Why MS SQL Server refuse using supporting filtered index in this scenario?

-- demo data
CREATE TABLE #Test (
    ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Test_ID PRIMARY KEY
    ,Col1 NVARCHAR(36) NOT NULL DEFAULT NEWID()
    ,Col2 NVARCHAR(20) NOT NULL DEFAULT N''  -- !!
    );

WITH
    L0   AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows
    L1   AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows
    L2   AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows
    L3   AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
    L4   AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows
    L5   AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows
    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)
INSERT INTO #Test(Col2)
SELECT TOP 100000 N''
FROM Nums;

INSERT INTO #Test(Col2)
VALUES(N'ABC');

-- FILTERED index to support filter predicate of a query
CREATE NONCLUSTERED INDEX IX_Test_Col2_filtered ON #Test (Col2 ASC) WHERE Col2 <> N'';

-- just checking statistics
DBCC SHOW_STATISTICS('#Test', 'IX_Test_Col2_filtered')

-- condition on variable = index scan :-(
DECLARE @Filter NVARCHAR(20) = N'ABC'

SELECT Col1
FROM #Test
WHERE Col2 = @Filter
    AND Col2 <> N'';

Everything goes as expected when using literals.

-- condition on literal value - index seek + key lookup :-)
SELECT Col1
FROM #Test
WHERE Col2 = N'ABC';

Answer 1

Filtered indexes can't use variables/parameters, unless you're building your query with dynamic SQL so that the query ends up getting executed with a literal.

A good article on the topic is Filtered Indexes and Dynamic SQL by Jeremiah Peschka.

OR

As Martin suggested, you could add WITH (RECOMPILE) to the query, but that should not be used without understanding the potential repercussions (see RECOMPILE Hints and Execution Plan Caching).

Answer 2

DBCC SHOW_STATISTICS('Test', 'IX_Test_Col2_filtered')

When you use constant value in filter then Optimizer make plan specific for that value. Since Optimizer know the parameter value,it use the Statistics Histogram to estimate the number of records that can be returned by a query.

When Local Variable is use in where condition, Optimizer make plan for "OPTIMIZE FOR UNKNOWN". It do not use Statistics Histogram,it uses the information on the Density Vector.

`All Density* Number of rows` which is equal to `1*100001`

So Number of rows to read will be 100001.

As we know in cost base optimization ,Optimizer will choose cost effective plan quickly.

So choosing index will not be feasible for Optimizer.

If you Index Hint then it will use Index Seek but cost will be more.

Optimizer make plan for OPTIMIZE FOR UNKNOWN because when next time you change value for local variable then it will reuse same plan.

In same case it is beneficial and in some case it will hurt performance.In OPTIMIZE FOR UNKNOWN assumption is based on calculation All Density * Number of rows.

Now I create Proc ,

Create Proc TestProc
@Filter NVARCHAR(20)
as

Begin

SELECT Col1
FROM #Test
WHERE Col2 = @Filter
    AND Col2 <> N'';

End

Exec TestProc N'ABC'

Verify the plan, plan is exactly similar to (dynamic)sp_executesql .

We see Index seek and key look up .

No of Execution=1
Estimated Number of rows=1

when you write dynamic sql,

DECLARE @Sql NVARCHAR(200) = ''
DECLARE @Filter NVARCHAR(20) = N'ABC'

set @Sql='SELECT Col1
FROM Test
WHERE  Col2 <> N''''
AND Col2 = @Filter1';

    print @Sql
    execute sp_executesql @Sql,N'@Filter1 NVARCHAR(20)',@Filter

sp_executesql is a Proc . Variable @Filter1 exactly behave like parameter of Proc. sp_executesql is not using @Filter.

So optimizer make plan for that parameter.When you pass some other value it will reuse same plan.This is called Parameter sniffing.

So basically what I said is correct.

Answer 3

An alternative pattern, place the variable to lookup into a temp table and join, try the following with execution plans and statistics io on

DECLARE @Filter NVARCHAR(20) = N'ABC'
DECLARE @FilterT TABLE (
    f nvarchar(36)
)
insert into @FilterT select N'ABC'

DROP TABLE IF EXISTS #FilterT
CREATE table #FilterT (
    f nvarchar(36)
)
insert into #FilterT select N'ABC'

SELECT Col1
FROM #Test
WHERE Col2 = @Filter
    AND Col2 <> N'';

SELECT Col1
FROM #Test
WHERE Col2 = N'ABC';

select col1
from #Test
inner join @FilterT
    on #Test.Col2 = [@FilterT].f
    AND Col2 <> N'';


select col1
from #Test
inner join #FilterT
    on #Test.Col2 = [#FilterT].f
    AND Col2 <> N'';