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.
|
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.
|
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
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
No, that only works if your branching logic hits separate stored procedures.
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!
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.
Sure, for those situations it’s probably a good fit. You can also use Dynamic SQL.