1

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'';

enter image description here

Everything goes as expected when using literals.

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

enter image description here

2

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).

  • Interesting. I wonder if there is any "official" documentation telling this fact? – jerik1 Oct 16 at 21:17
  • @jerik1 Generally how execution plans are derived doesn't make it into the documentation. Presumably because its crazy complex and changes often. I suppose the better wording should be "The engine can't use filtered indexes with..." since the filtered index still exists and works if the engine chooses it. – LowlyDBA Oct 16 at 23:22
  • 4
    it can do if you use OPTION (RECOMPILE) so it can sniff the variable value and also doesn't have to compile a plan suitable for any variable value – Martin Smith Oct 17 at 0:50
0
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.

  • I am aware of OPTIMIZE FOR UNKNOWN with variables. But the number of rows in index is 1 not 1M, so the assumption should be 1 row – jerik1 Oct 17 at 6:15
  • @jerk, Read my edited now. – KumarHarsh Oct 17 at 8:49
  • ) I didn't get where you heading. For me your answer is out of topic. – jerik1 Oct 17 at 9:24
  • @jerik1 , No hard feeling.I was trying to answer "Why MS SQL Server refuse using supporting filtered index in this scenario?" .I do not much agree with the link given.May be my language is little poor,but I am confident with the reasoning. – KumarHarsh Oct 17 at 9:40
0

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'';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.