Filtered Indexes vs Parameterization (Again)

Last Updated 6 years ago

At First I Was Like…

This won’t work at all, because parameters are a known enemy of filtered indexes. But it turns out that some filtered indexes can be used for parameterized queries, whether they’re from stored procedures, dynamic SQL, or in databases with forced parameterization enabled.

Unfortunately, it seems limited to filtering out NULLs rather than exact values.

Easy Example

If I set up this proc and index, and then run it, it’ll use the filtered index even though it seems like it maybe shouldn’t.

CREATE OR ALTER PROCEDURE dbo.filter_check (@age INT, @creation_date DATETIME)
AS
BEGIN
    SELECT COUNT(*) AS records
    FROM dbo.Users AS u
    WHERE u.Age >= @age
    AND u.CreationDate >= @creation_date;
END

CREATE INDEX ix_whatever ON dbo.Users(CreationDate) INCLUDE(Age) WHERE Age IS NOT NULL;

EXEC dbo.filter_check @age = 18, @creation_date = '2017-10-18';

1

2

3

4

5

6

7

8

9

10

11

12

CREATE OR ALTER PROCEDURE dbo.filter_check (@age INT, @creation_date DATETIME)

AS

BEGIN

SELECT COUNT(*) AS records

FROM dbo.Users AS u

WHERE u.Age >= @age

AND u.CreationDate >= @creation_date;

END

CREATE INDEX ix_whatever ON dbo.Users(CreationDate) INCLUDE(Age) WHERE Age IS NOT NULL;

EXEC dbo.filter_check @age = 18, @creation_date = '2017-10-18';

Even if I pass in a NULL for @age, it works. If I were searching for IS NULL or IS NOT NULL, well, that wouldn’t work, but that’s also pretty tough to parameterize. Heh.

Horse Water

This really only helps if you have a parameterized predicate on a column with a good chunk of NULL values in it. You can bypass indexing, and queries needing to navigate all those NULL values, with a filtered index.

But if you want to do something more specific, things don’t work as well. Using a slightly different example, with forced parameterization, this doesn’t work.

ALTER DATABASE StackOverflow2013 SET PARAMETERIZATION FORCED;

CREATE INDEX ix_votes_filtered_too
    ON dbo.Votes(VoteTypeId) 
        WHERE VoteTypeId = 9;

SELECT COUNT(*)
FROM dbo.Votes AS v
WHERE v.VoteTypeId = 9;

1

2

3

4

5

6

7

8

9

ALTER DATABASE StackOverflow2013 SET PARAMETERIZATION FORCED;

CREATE INDEX ix_votes_filtered_too

ON dbo.Votes(VoteTypeId)

WHERE VoteTypeId = 9;

SELECT COUNT(*)

FROM dbo.Votes AS v

WHERE v.VoteTypeId = 9;

The query plan will skip our filtered index, and warn about it in the select operator properties — just hit F4 to bring those up.

Neck Pain

If you’re one of those crazy kids who lets NULLs walk all over their data, you might find some performance gains by filtering them out of your indexes, but only if they make up a good chunk of your data.

This is also a bummer for people who rely on forced parameterization to help deal with other issues — it takes a potentially really powerful tool out of your arsenal for query and index tuning.

Thanks for reading!

2 Comments. Leave new

wqw
October 26, 2018 10:13 am

Makes most sense to filter on IS NOT NULL those optional FK columns.

Unfortunately I’ve seens these filtered indexes make column stats and/or stats update go bonkers, so test your workload and measure the duration of those SELECT’s on STATMAN.

Reply
Joe Ormsby
June 14, 2021 10:02 pm

Had success with what I termed ‘filtered filtered index’ and thought I’d share. Large audit table needs to be filtered by two fields: patient ID and access datetime but it’s too large in our environment to just add regular indexes. Created a filtered index on patient Id where access datetime fixed at > 1/1/2018 to access latest four years of 16 years of history (most common scenario). In code, created derived table using only indexed fields and lie to it, asking for specific patient and all dates > 1/1/2018 which used the filtered index. Wrap in ( ) and add another access time Where filter with actual target dates. Since patient ID is very selective, the cost of initially searching four years of data was reasonable and since it’s immediately ‘re-filtered’ is highly efficient compared to table scan. Once past two layers I have the primary keys needed to join to the rest of the tables in nested 3rd etc layers. Just had to tell a white lie in the inner most query, didn’t really want dates that happen to match the filtered index. In use on a daily basis in large reporting environment. Results (complex report with other tables) < 15 seconds.

Reply