SQL Server – Filtered Index Only Used When Filtered Part is in JOIN, Not WHERE

filtered-indexindexperformancequery-performancesql servert-sql

I've created the filtered index below however when I run the 2 queries further down this index only gets used for a seek in the first example that has the END_DTTM in the JOIN rather than the where clause (that's the only difference in the queries). Can anybody explain why this happens?

Index Creation

CREATE NONCLUSTERED INDEX [ix_PATIENT_LIST_BESPOKE_LIST_ID_includes] ON [dbo].[PATIENT_LIST_BESPOKE] 
(
    [LIST_ID] ASC,
    [END_DTTM] ASC
)
WHERE ([END_DTTM] IS NULL)

Queries

DECLARE @LIST_ID INT = 3655

--This one seeks on the index

SELECT  
    PATIENT_LISTS.LIST_ID
FROM    
    DBO.PATIENT_LISTS
    LEFT JOIN DBO.PATIENT_LIST_BESPOKE ON PATIENT_LISTS.LIST_ID = PATIENT_LIST_BESPOKE.LIST_ID  
                                      AND PATIENT_LIST_BESPOKE.END_DTTM IS NULL
WHERE
    PATIENT_LISTS.LIST_ID = @LIST_ID

--This one scans on the index

SELECT  
    PATIENT_LISTS.LIST_ID
FROM    
    DBO.PATIENT_LISTS
    LEFT JOIN DBO.PATIENT_LIST_BESPOKE ON PATIENT_LISTS.LIST_ID = PATIENT_LIST_BESPOKE.LIST_ID  
WHERE   
    PATIENT_LISTS.LIST_ID = @LIST_ID AND
    PATIENT_LIST_BESPOKE.END_DTTM IS NULL

Best Answer

For the optimizer to match a predicate to an index (filtered or otherwise), the predicate must appear adjacent to the Get operation in the logical query tree. To facilitate this, predicates are generally pushed as close as possible to the leaves of the logical tree before optimization begins.

To greatly simplify, the physical index strategy implementation does this:

Predicate + Logical Get -> Physical Get (using Index)

The query you are interested in starts with the predicate above an outer join:

Predicate on T2 --+-- LOJ -- Get (T1)
                       |
                       +---- Get (T2)

This shape does not match the index strategy rule because the predicate is not adjacent to the Get. So, the first part of the answer is that filtered index matching will fail unless the predicate can be pushed past the outer join.

The second part is simply that the optimizer does not contain the necessary exploration rule to move a predicate past an outer join on the preserved side, because the transformation is so rarely valid. It is a general feature of the optimizer that only the most frequently useful rules are implemented.

As a result, matching the filtered index fails in this case. To be clear, the rewrite would be valid in the very specific case you cite (second query).

For the first query form (with different semantics), the predicate is associated with the join from the start, and the predicate push-down logic can move this the short distance to the Get because it does not have to move past an outer join as explained above.

Background and further information:

Related Question