O índice filtrado não é usado quando a variável está na condição WHERE

Índices filtrados não podem usar variáveis/parâmetros, a menos que você esteja construindo sua consulta com SQL dinâmico para que a consulta acabe sendo executada com um literal.

Um bom artigo sobre o tema é Índices Filtrados e SQL Dinâmico de Jeremiah Peschka.

OU

Como Martin sugeriu, você pode adicionar WITH (RECOMPILE)à consulta, mas isso não deve ser usado sem entender as possíveis repercussões (consulte RECOMPILE Hints and Execution Plan Caching ).

DBCC SHOW_STATISTICS('Test', 'IX_Test_Col2_filtered')

Quando você usa o valor constante no filtro, Optimizertorne o plano específico para esse valor. Desde que Optimizersaiba o parametervalor, ele usa o Statistics Histogrampara estimar o número de registros que podem ser retornados por uma consulta.

Quando a variável local for usada na condição where, Optimizerfaça um plano para " OPTIMIZE FOR UNKNOWN". Ele não usa Statistics Histogram, ele usa as informações no Density Vector.

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

Portanto, o número de linhas a serem lidas será 100001.

Como sabemos na otimização da base de custos, Optimizerescolheremos o plano econômico rapidamente.

Portanto, a escolha do índice não será viável para Optimizer.

Se você Index Hint, então, ele usará, Index Seekmas o custo será maior.

O otimizador faz um plano OPTIMIZE FOR UNKNOWNporque, na próxima vez que você alterar o valor local variable, ele reutilizará o mesmo plano.

No mesmo caso, é benéfico e, em alguns casos, prejudicará o desempenho . A OPTIMIZE FOR UNKNOWNsuposição é baseada em cálculo All Density * Number of rows.

Agora eu crio Proc ,

Create Proc TestProc
@Filter NVARCHAR(20)
as

Begin

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

End

Exec TestProc N'ABC'

Verifique o plano, o plano é exatamente semelhante ao (dynamic)sp_executesql.

Vemos Index seeke 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é um Proc. Variável @Filter1se comporta exatamente como parâmetro de Proc. sp_executesqlnão está usando @Filter.

Portanto optimizer, faça um plano para esse parâmetro. Quando você passar algum outro valor, ele reutilizará o mesmo plano. Isso é chamado de Parameter sniffing.

Então basicamente o que eu disse está correto.

Um padrão alternativo, coloque a variável para pesquisar em uma tabela temporária e junte-se, tente o seguinte com planos de execução e estatísticas 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'';