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 (9)

  1. Gary says:

    I have just been bitten by the SQL Server 2008 query optimizer using a filtered index when it shouldn't have (because it didn't cover the rows I expected to be returned).  This was in a join between a CTE and a user table that wrongly failed to return any rows.  Changing the join condition from T1.Id = T2.Id to T1.Id = T2.Id + 0 caused enough perturbation to allow the query to work (as did using a #temp table instead of a CTE, or forcing a merge or hash join).

  2. Paul White NZ says:

    Hi Tim,

    Quoting: "In many cases, a way to get around the local variable problem is to recompile the statement […] Unfortunately, in the case of the filtered index it does not work".

    This is only true if you are looking at the *estimated* execution plan.

    OPTION (RECOMPILE) does not cache a query plan so it is safe for the engine to use the sniffed value of the parameter, and hence the filtered index.

    The 'actual' (post-execution) plan *does* show the filtered index being used for:

    SET STATISTICS PROFILE ON;

    DECLARE @PID integer = 870;

    SELECT

       sod.ProductID

    FROM Sales.SalesOrderDetail AS sod

    WHERE

       sod.ProductID = @PID

    OPTION (RECOMPILE);

    SET STATISTICS PROFILE OFF;

    Output:

     |–Index Seek(OBJECT:([AdventureWorks2012].[Sales].[SalesOrderDetail].[FIDX_SalesOrderDetail_ProductID] AS [sod]), SEEK:([sod].[ProductID]=(870)) ORDERED FORWARD)

  3. Paul White NZ says:

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

  4. Tim Chapman says:

    Thanks Paul.  The examples I gave were for the actual execution plan.  The fix for the filtered index usage with OPTION(RECOMPILE) was included in 2008 R2 SP1 (or one of the CUs it contained – I didn't look up the specific one).  I happened to have been using the RTM for 2008 R2 for these examples.  I'll make an adjustment to the language above.  

    Thanks again,

    Tim

  5. Jeff Roughgarden says:

    Toward the end of your article you said " In most cases, the best way to take advantage of the filtered indexes you have is through the use of dynamic string execution. " This may be jumping from the frying pan into the fire, because the non-parameterized dynamic SQL will result in a different  cached plan for each value. So, I think it is OK for occasional use, but I would not want to adopt that suggestion in a production application.

  6. Tim Chapman says:

    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

  7. Eric says:

    Official advice to avoid parameterized queries? Hackers everywhere, rejoice.  

    http://xkcd.com/327/

  8. Kimberly@SQLskills.com says:

    Hey there Eric – There are many ways to significantly reduce the potential of SQL injection to the point where it really has no capabilities: http://www.sqlskills.com/…/little-bobby-tables-sql-injection-and-execute-as.

    But, I have to agree with Tim here – in MANY releases where OPTION (RECOMPILE) hasn't worked – then the benefit of writing the string dynamically is that it ALWAYS works (using the FI when appropriate and usually getting better estimates as well). Luckily OPTION (RECOMPILE) works pretty well NOW for almost all cases.

    Thanks,

    Kimberly

  9. Aakaash says:

    Thank you so much. OPTION (RECOMPILE) works for me in SQL 2012.