Filtered Index on NULL values is still doing a Key Lookup takes you through a strange behavior of filtered index in SQL Server. Here, we’ve discussed how a filtered index can help to improve the performance of your query. When I was working with one of the customer, in their critical production environment I’ve seen they created a filtered index on NULL values to cover more frequently used queries. But, when we are checking through the long running procedures we could see that Filtered Index on NULL values is still doing a Key Lookup. Let me explain it with an example.

Note: I am using AdventureWorks2016 database.

Here is the query:

SELECT SalesOrderID,

               OrderQty,

               UnitPrice

FROM    SALES.SalesOrderDetail

WHERE OrderQty>1 AND

                ProductID = 877 AND

                CarrierTrackingNumber IS NULL;

Why we need a covering index for the above query:

• As per the business requirement, majority of our focus is on product number 877.
• 90% of the cases we are fetching data based on the same predicate (OrderQty>N AND ProductID = 877 AND CarrierTrackingNumber IS NULL).
• OrderQty is a variable (1 to N).
• We are using the same condition/predicate in 8 different procedures. On and average this condition hits 10 times per minute.

How to determine the index columns?

OrderQty: As per the requirement, it’s a variable passed at runtime. Value is between 1 to N. Since it’s a predicate/where/condition we should keep it in Key columns.

ProductID: Since 90% of the queries are fetching data only for the product 877, we can have a filtered index that cover this particular scenario.

CarrierTrackingNumber: Like ProductID, we can have a filtered index on this column.

SalesOrderID: Since it’s part of the PRIMARY KEY we need not fetch it separately. Primary key columns are available in all non-clustered indexes by default. Thereof, no need to get it separately.

UnitPrice: This is the only column where we are not covering either in index key or in filter. Hence we need to add this column to index. Since we do not have any predicate on it and we are just fetching data, we can add it to INCLUDED list.

We are covering all columns for the above given query. Now, let’s create the index:

CREATE NONCLUSTERED INDEX

Custom_NIX_SALES_SalesOrderDetail_OrderQty_ProductID_877_CarrierTrackingNumber_ISNULL

ON SALES.SalesOrderDetail (OrderQty)

INCLUDE (UnitPrice)

WHERE ProductID = 877 AND

CarrierTrackingNumber IS NULL;

Create the above index, execute the query and see what will happen:

Results:

Surprising, you are covering all columns from the given query. But, still it’s doing the clustered index scan. We’ll force it to use the newly created index, then only we’ll come to know the reason.

Force the query to use the covering index:

Results:

• Even we are covering all columns, you can see that it’s doing the Key Lookup.
• When you check it, the Key Lookup is happening to get the matching values for the predicate “CarrierTrackingNumber IS NULL”
• Covering index that we’ve created is already having the filter “CarrierTrackingNumber IS NULL”. But, why it’s not using that index?

Why a Filtered Index on NULL values is still doing a Key Lookup?

• When we are creating the filtered index on a value (ProductID = 877), it’s working fine. But, when are doing the same with “IS NULL” it’s not working.
• I am still unable to find the root cause.
• But, boss (Microsoft) is saying it doesn’t work, also suggested the resolution.
• Here, you can find Microsoft take on this issue.
• Microsoft is suggesting the below resolution:

“To resolve this issue, include the column that is tested as NULL in the returned columns. Or, add this column as include columns in the index“

Resolution:

• Add the column in include columns.
• Drop and recreate the index by adding this column in INCLUDE columns list and rerun the query.

Results:

You can clearly see that, by default the query started using the covering filtered index without any key lookup.

Performance test:

We’ll test the performance of the query by comparing with CLUSTERED INDEX SCAN and the covering NON-CLUSTERED INDEX seek.

Performance Improvements:

Summary:

• “Filtered index” is really useful when you want to customize and narrow down the data usage and it works like a magic with the minimized index maintenance cost.
• When you are creating a filtered index on IS NULL condition, be aware that we need to include the same column.
• Filtered Indexes in SQL Server is an awesome feature. But, Terms and Conditions applied.
• Below are the few interesting articles that describes more side effects of Filtered Indexes

An Unexpected Side-Effect of Adding a Filtered Index by Paul White

A-Z of Filtered Indexes with examples in Sql Server by Basavaraj Biradar

What You Can (and Can’t) Do With Filtered Indexes by Brent Ozar