Why Isn’t My Filtered Index Working?

With the introduction of filtered indexes in SQL 2008, DBA’s were finally able to create small, well defined indexes with a simple predicate that would allow queries to search a subset of a rows on a table rather than all the rows on a table. While the technology has been around for some time, I haven’t seen them mentioned too often in forums, blogs or newsletters. That said, just the other day I found an occasion to use a filtered index, and after creating it, was left wondering why the query didn’t take advantage of it.

First, let’s set up our test scenario. We’ll be using the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables in the AdventureWorks database. In this case, I’m getting the Sum of each order during a specified time period while specifying whether or not it was an online order.

Here’s the query:

DECLARE @OnlineFlag BIT = 1
DECLARE @StartDate DATETIME = '7/1/2001'
DECLARE @EndDate DATETIME = '7/31/2002'

SELECT SalesOrderHeader.SalesOrderID,
       SUM(SalesOrderDetail.LineTotal)
  FROM Sales.SalesOrderHeader INNER JOIN Sales.SalesOrderDetail
    ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID
WHERE SalesOrderHeader.OnlineOrderFlag = @OnlineFlag
   AND SalesOrderHeader.OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY SalesOrderHeader.SalesOrderID

The resulting execution plan:

From here, I thought I might be able to use a filtered index on the OnlineOrderFlag in the SalesOrderHeader table.

Here’s the index creation statement:

CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_OnlineOrderFlagFiltered
    ON Sales.SalesOrderHeader(OrderDate,SalesOrderID,OnlineOrderFlag)
 WHERE OnlineOrderFlag = 1

Note the Where statement, this is where the filter comes in to play. Functions like getdate() or dateadd() are not available to use in this context.

Once the filter is added, I think I’m in business, so I execute the query again and get the following execution plan.

But instead of seeing the use of my shiny new filtered index, the query is still using the Clustered Index Scan in addition to a new warning on the SELECT operator. If I click the operator and check the warnings, Unmatched Indexes is showing True.

This warning is telling me that Parameterization is to blame for the filtered index not being used. From here, I see 3 options.

1. Remove the parameters and use literals. (not practical)
2. Use Dynamic SQL
3. Use OPTION(RECOMPILE) at the bottom of the query.

For the purposes of this exercise, I’ll be using OPTION(RECOMPILE) so that the query can take advantage of the filtered index.

Here the query with the added hint:

DECLARE @OnlineFlag BIT = 1
DECLARE @StartDate DATETIME = '7/1/2001'
DECLARE @EndDate DATETIME = '7/31/2002'

SELECT SalesOrderHeader.SalesOrderID,
       SUM(SalesOrderDetail.LineTotal)
  FROM Sales.SalesOrderHeader INNER JOIN Sales.SalesOrderDetail
    ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID
WHERE SalesOrderHeader.OnlineOrderFlag = @OnlineFlag
   AND SalesOrderHeader.OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY SalesOrderHeader.SalesOrderID
OPTION (RECOMPILE)

And the resulting execution plan:

This solution for your filtered index may or may not be ideal for you because of the added CPU/Compile time added to each execution of the query. That said, if you have wildly varying parameters for each execution of your query, you may have already added OPTION (RECOMPILE) to deal with issues like parameter sniffing.

Here are some fantastic resources if you would like to read further on Filtered Indexes:

Introduction to SQL Server Filtered Indexes

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

Filtered Indexes: What You Need To Know