Filtered Indexes Work-Around

Recently, I needed to create a stored procedure that queried a rather large table. The table has a filtered index on a date column, and it covers the query. However, the Query Optimizer was not using the index, which was increasing the execution time (not to mention IO!) by at least 10x. This wasn’t the first time I’ve had the Optimizer fail to use a filtered index. Normally when this happens, I use a table hint to force the filtered index — after I verify that it is indeed faster, of course. However, since this was a stored procedure, I was receiving the following error message whenever I tried to execute the proc:

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

SQL Server would not allow me to execute the stored procedure using the filtered index hint. If I removed the hint, it executed, but it used a different, non-covering and far more expensive index. For those of you not familiar with this issue, allow me to illustrate the problem.

First, create a table to play with and populate it with some bogus data:

Create Table dbo.filteredIndexTest
      myID   int Identity(1,3)
    , myDate smalldatetime 
    , myData char(100)
    Constraint PK_filteredIndexTest
        Primary Key Clustered(myID)
Set NoCount On;
Declare @date smalldatetime = '2010-01-01';
While @date < '2010-02-01'
    Insert Into dbo.filteredIndexTest
        , myData
    Select @date
        , 'Date: ' + Convert(varchar(20), @date, 102);
    Set @date = DateAdd(minute, 1, @date);
Select Count(*) From dbo.filteredIndexTest;

It looks like this will generate 44,640 rows of test data… plenty enough for our purposes. Now, let’s create our filtered index and write a query that will use it:

Create NonClustered Index IX_filteredIndexTest_1
    On dbo.filteredIndexTest(myDate)
    Include (myData)
    Where myDate >= '2010-01-27';
Select Distinct myData
From dbo.filteredIndexTest
Where myDate >= '2010-01-28';

If you look at the execution plan for this query, you’ll notice that the Optimizer is using the filtered index. Perfect! Now let’s parameterize it.

Declare @myDate1 smalldatetime = '2010-01-28';
Select Distinct myData
From dbo.filteredIndexTest
Where myDate >= @myDate1;

Uh oh. Looking at the execution plan, we see that SQL Server is no longer using the filtered index. Instead, it’s scanning the clustered index! Why is this? There’s actually a good explanation for it. The reason is that I could, in theory, pass a date to my parameter that fell outside of the filtered date range. If that’s the case, then SQL Server could not utilize the filtered index. Personally, I think it’s a bug and SQL Server should identify whether or not a filtered index could be used based on the actual value submitted, but… that’s a whole other blog post. 🙂

So what can we do? Well, dynamic SQL may be able to help us out in this case. Let’s give it a go. First, let’s try parameterized dynamic SQL.

Declare @mySQL1 nvarchar(2000)
    , @myParam nvarchar(2000) = '@p_myDate2 smalldatetime'
    , @myDate2 smalldatetime = '2010-01-28';
Set @mySQL1 = 'Select Distinct myData
              From dbo.filteredIndexTest
              Where myDate >= @p_myDate2';
Execute sp_executeSQL @mySQL1, @myParam, @p_myDate2 = @myDate2;

Looking at the execution plan, we see we’re still scanning on the clustered index. This is because the parameterized dynamic SQL resolves to be the exact same query as the one above it. Let’s try unparameterized SQL instead:

Declare @mySQL2 nvarchar(2000)
    , @myDate3 smalldatetime = '2010-01-28';
Set @mySQL2 = 'Select Distinct myData
              From dbo.filteredIndexTest
              Where myDate >= ''' + Cast(@myDate3 As varchar(20)) + '''';
Execute sp_executeSQL @mySQL2;
-- Drop Table dbo.filteredIndexTest;

Voila! We have a seek on our filtered index. Why? Because the statement resolves to be identical to our first query, where we hard-coded the date value in the WHERE clause.

Now, I want to stress this fact: you should always, ALWAYS use parameterized dynamic SQL whenever possible. Not only is it safer, but it’s also faster, because it can reuse cached plans. But sometimes you just cannot accomplish the same tasks with it. This is one of those times. If you do end up needing to use unparameterized dynamic SQL as a work-around, please make sure you’re validating your input, especially if you’re interfacing with any sort of external source.

There’s an even easier work-around for this problem that Dave ( shared with me: recompile.

Adding “Option (Recompile)” to the end of your statements will force the Optimizer to re-evaluate which index will best meet the needs of your query every time the statement is executed. More importantly, it evaluates the plan based on the actual values passed to the parameter… just like in our hard-coded and dynamic SQL examples. Let’s see it in action:

DECLARE @myDate4 SMALLDATETIME = '2010-01-28';
FROM dbo.filteredIndexTest 
WHERE myDate >= @myDate4
DECLARE @myDate5 SMALLDATETIME = '2010-01-20';
FROM dbo.filteredIndexTest 
WHERE myDate >= @myDate5

If we look at the execution plans for the 2 queries above, we see that the first query seeks on the filtered index, and the second query scans on the clustered index. This is because the second query cannot be satisfied with the filtered index because we initially limited our index to dates greater than or equal to 1/27/2010.

There are, of course, trade-offs associated with each approach, so use whichever one best meets your needs. Do you have another work-around for this issue? If so, please let me know. 🙂


Alex Kuznetsov ( shared this method too:

DECLARE @myDate1 SMALLDATETIME = '2010-01-28';
FROM dbo.filteredIndexTest
WHERE myDate = @myDate1
AND myDate >= '2010-01-27';

Like the other examples, this will result in an index seek on the filtered index. Basically, by explicitly declaring the start date of your filter, you’re letting the Optimizer know that the filtered index can satisfy the request, regardless of the parameter value passed. Thanks for the tip, Alex! 🙂

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , , . Bookmark the permalink.

7 Responses to Filtered Indexes Work-Around

  1. Jerry says:

    Ok, in a freakish coincidence, I got the same error today for the very first time. Wow. Your post concentrated on why the sproc was not using the filtered index, and how to get it to use the index, which, of course, is valuable information.

    However, I am much more interested in what causes this error in the first place. You state, “…since this was a stored procedure, I was receiving the following error message…” implying this is due to sprocs. However, we’ve been using index hints in sprocs for years, and have never received this error. I am curious why I am getting it now all of a sudden on one specific sproc. Do you or any of your readers have insight on that?


  2. Alex Kuznetsov says:

    Hi Michelle,

    I think that if you explicitly tell the optimizer that your parameter fits into the filter. Instead of:

    FROM dbo.filteredIndexTest
    WHERE myDate >= @myDate1;

    let us try this:

    FROM dbo.filteredIndexTest
    WHERE myDate >= @myDate1
    AND myDate>’theThresholdInMyFilteredIndex’

    What do you think, Michelle?

  3. Hi Jerry,

    I actually mispoke (mistyped?) a little. I thought the error message was returned that way because it was called via stored proc, but really you would get the same error message if you just try to force the hint in an ad-hoc query, too.

    You receive the error message because Query Optimizer, sometimes correctly and sometimes incorrectly, perceives that it will be unable to fulfill the request using the index you specify. The only case I’ve ever seen this in (so far) has been a filtered index. Hopefully that helps, and good luck! 🙂


  4. Hi Alex,

    Yes, that works too! I’ll edit my post with your suggestion too. Thank you. 🙂


  5. Jerry says:

    Ok, that makes sense. But just for the record, I received the error today using a join hint in the OPTION statement.

  6. Hi Michelle –

    Is there a workaround for this issue that doesn’t require me to tell my application developers they have to change all their code? 😉 Something I could do at the index level? Thanks.

  7. Very cool. Thanks for sharing Dave’s tip too.

Leave a Reply

Your email address will not be published. Required fields are marked *