Filtered Indexes and Variables: Less Doom and Gloom

Last Updated 6 years ago

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.

CREATE UNIQUE NONCLUSTERED INDEX 
ix_helper ON dbo.Users 
  (UpVotes, DownVotes, Id) 
INCLUDE (DisplayName, Reputation) 
  WHERE Reputation >= 400000

1

2

3

4

5

CREATE UNIQUE NONCLUSTERED INDEX

ix_helper ON dbo.Users

(UpVotes, DownVotes, Id)

INCLUDE (DisplayName, Reputation)

WHERE Reputation >= 400000

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.

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.

CREATE PROCEDURE dbo.YouBigDummy (@u INT = 15340, @d INT = 4761)
AS
BEGIN
      SELECT    u.DisplayName,
                u.Reputation
      FROM      dbo.Users AS u
      WHERE     u.Reputation >= 400000
                AND u.UpVotes = @u
                AND u.DownVotes = @d;
END;

EXEC dbo.YouBigDummy

1

2

3

4

5

6

7

8

9

10

11

12

CREATE PROCEDURE dbo.YouBigDummy (@u INT = 15340, @d INT = 4761)

AS

BEGIN

SELECT u.DisplayName,

u.Reputation

FROM dbo.Users AS u

WHERE u.Reputation >= 400000

AND u.UpVotes = @u

AND u.DownVotes = @d;

END;

EXEC dbo.YouBigDummy

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.

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!

5 Comments. Leave new

Kristen
January 4, 2017 12:16 pm

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
Erik Darling
January 4, 2017 12:18 pm

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

Reply
- Kristen
  January 4, 2017 12:28 pm
  
  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
Gabe
January 4, 2017 12:24 pm

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
- Erik Darling
  January 4, 2017 12:53 pm
  
  Sure, for those situations it’s probably a good fit. You can also use Dynamic SQL.
  
  Reply