The Pains of Filtered Indexes

In a previous post I briefly went over how useful filtered indexes can be. Filtered indexes are a great feature that have numerous uses. However, like almost everything in computing, there are trade-offs, and filtered indexes are no exception. The challenge with filtered indexes is the decisions that the query optimizer can make regarding them. In some cases, the indexes will be used as you would expect. There are cases, unfortunately, where decisions can't be made as to if the filtered index can be used which ultimately leads to it not being considered.

Let's look at an example of what I am talking about. First, I'll create a filtered index to play around with.

CREATE INDEX FIDX_SalesOrderDetail_ProductID
ON Sales.SalesOrderDetail (ProductID)
WHERE ProductID = 870

Run a quick query against the SalesOrderDetail table for ProductID 870. As you may expect, this should result in a SEEK of our newly created filtered index.

Great – that is exactly what we want. The new filtered index was used in the previous query. One thing to notice in the previous query is that I used an explicit value 870 in the predicate. SQL Server didn't have to make any guesses as to what value was being sought. However, what happens if I use a local variable?

DECLARE @ProductID INT
SET @ProductID = 870  

SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID

 
In this case, SQL Server did NOT use the filtered index – even though the query was searching for the value 870. The reason for this is the same reason you want to try to avoid using local variables in predicates. The query optimizer did not what value was held in the local variable at compile time, so it had to make some assumptions based on the statistics it contained for the column being searched. Because SQL Server didn't know what value was in the variable, a filtered index cannot be considered.

 

In many cases, a way to get around the local variable problem is to recompile the statement:

DECLARE @ProductID INT
SET @ProductID = 870  

SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID
OPTION(RECOMPILE)

Unfortunately, in the case of the filtered index it does not work:
Note: The fix for this behavior was fixed in SQL Server 2008 R2 SP1. Now an OPTION(RECOMPILE) will the actual local variable (or parameter) value.

 

OK, at this point we know that using local variables presents a challenge. So, what about using parameterized SQL statements? Here I'll create a parameterized SQL statement using sp_executesql.

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID'
EXECUTE sp_executesql @SQL, N'@ProductID INT', @ProductID = 870

 

 

Still no luck. Even those this statement was compiled using the ProductID of 870, the filtered index still isn't used. But, why not? Well, let's think back to what a filtered index actually is. A filtered index is a NC structure that contains values based on an explicit predicate. This means that the filtered index is only useful if the query is searching explicitly for the query defined in the filter definition for the index. If SQL Server were to create an execution plan that used that filtered index, it would ONLY be useful for reuse if subsequent statements passed in that specific criteria. Since SQL Server has no idea if that is going to happen, the filtered index isn't considered for reuse.

But what if you REALLY want SQL Server to use that specific filtered index? It's easy enough to try to force it using an index hint.

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT ProductID
FROM Sales.SalesOrderDetail WITH(INDEX=FIDX_SAlesOrderDetail_ProductID)
WHERE ProductID = @ProductID '
EXECUTE sp_executesql @SQL, N'@ProductID INT', @ProductID = 870

In this case, I actually get an error message telling me that my usage of the hint is not valid. This is due to the fact that the index I am forcing is filtered.

   

 

While incredibly useful, you can see that filtered indexes do have some drawbacks when it comes to how they can be applied. In most cases, the best way to take advantage of the filtered indexes you have is through the use of dynamic string execution. In this case I was lazy and just modified how I was using sp_executesql. The EXECUTE statement would have worked just as well.

DECLARE @SQL NVARCHAR(MAX), @ProductID INT
SET @ProductID = 870
SET @SQL = N'SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE ProductID = ' + CAST(@ProductID AS VARCHAR(10))
EXECUTE sp_executesql @SQL

And, sure enough, we are able to take advantage of the filtered index because SQL Server is given an explicit value at compile time.

 

Now for some cleanup:

DROP INDEX FIDX_SalesOrderDetail_ProductID
ON Sales.SalesOrderDetail

 

HTH,
Tim

Comments

• Anonymous
  October 29, 2012
  The comment has been removed

• Anonymous
  February 15, 2013
  The comment has been removed

• Anonymous
  February 15, 2013
  @Gary, that would be a serious bug if true. You should submit it with a repro on connect.microsoft.com

• Anonymous
  April 14, 2013
  The comment has been removed

• Anonymous
  November 07, 2013
  The comment has been removed

• Anonymous
  November 13, 2013
  Hi Jeff, That isn't necessarily a bad thing - building the string on the fly.  Yes, it can result in plan cache bloat, but if you were to enable 'optimize for adhoc workloads' (and you should), then the problem can be reduced dramatically. Thanks, Tim

• Anonymous
  January 07, 2015
  Official advice to avoid parameterized queries? Hackers everywhere, rejoice.   http://xkcd.com/327/

• Anonymous
  May 07, 2015
  The comment has been removed

• Anonymous
  November 24, 2015
  Thank you so much. OPTION (RECOMPILE) works for me in SQL 2012.