Filtered Indexes and Variables: Less Doom and Gloom

It Is Known

That when you use filtered indexes, they get ignored when your queries are parameterized. This is a Plan Caching Thing©, of course. The simplest example is a bit column with a filtered index. If your index is on WHERE Bit = 1, it doesn’t have data for WHERE Bit = 0. That index would only be suitable for one variation of the query, so caching a plan that uses an index which can’t be reused for every variation isn’t feasible.

There would be errors everywhere. Your users would hate you. You’d end up in a belltower, probably ordering a lot of pizza and waiting for one of those Kevin Costner movie marathons where they play everything from that real awkward stretch from 1992 to uh… oh wow, it never stopped. There are a lot of bad movies in there. Tin Cup was funny though.

Mix and Match

But on the bright side, plans can be cached if they’re parameterized and the parameter isn’t for the filter condition. Let’s look at a quick example.

This is a pretty index, isn’t it? Unique, narrow, selective filter condition. It’s a winner. What could go wrong?

Well…

Just so you don’t think I’m lying, here’s what happens if we disobey the first rule of filtered indexes.

Monsters.

Clustered index scan, missing index request. The works. When offered our filtered index, the optimizer makes the same face I do when the all the scotch behind the bar starts with “Glen”.

But…

Other things can be variables! Remember that our index is keyed on UpVotes, DownVotes, and Id. It would be daffy to create the index we did just to search on Reputation. So let’s expand our horizons.

UpVotes and DownVotes take parameters as predicates, but Reputation is a literal value. In this case, the optimizer makes the same face I do when I find a Chateauneuf-Du-Pape I haven’t had yet. Infinite joy.

Joy, Ode To

Filtration System

While index filter conditions don’t deal with parameters well, you can still use parameters for predicates in other columns. That’s an important distinction to make when evaluating filtered index usage.

Thanks for reading!

, , ,
Previous Post
What Questions Would You Ask on a Salary Survey?
Next Post
Tell Us What You Make: The 2017 Data Professional Salary Survey

5 Comments. Leave new

  • Would this work around the parameterised query on a filtered index caching issue?

    IF @r >= 400000
    BEGIN
    SELECT u.DisplayName,
    u.Reputation
    FROM dbo.Users AS u
    WHERE u.Reputation >= 400000
    AND u.Reputation >= @r;
    END
    ELSE
    BEGIN
    SELECT u.DisplayName,
    u.Reputation
    FROM dbo.Users AS u
    WHERE u.Reputation >= @r;
    END

    Reply
  • No, that only works if your branching logic hits separate stored procedures.

    Reply
    • Thanks Erik. I’d better take a look through my code and make sure I haven’t fallen foul of this … I may be gone some time!

      Reply
  • Would you ever consider putting OPTION(RECOMPILE) on the statements that you want to use the filtered index? That’s been my solution to get around the first rule – and we’ve never had a situation where these queries would be executed more than once every handful of minutes, so recompilation time wasn’t an issue.

    Reply

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}