Filtered Indexes vs Parameterization (Again)

Execution Plans, Indexing
2 Comments

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.

wakka wakka

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.

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

Letdown
Other People’s F4

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!

Previous Post
Batch Mode For Row Store: Does It Fix Parameter Sniffing?
Next Post
[Video] Office Hours 2018/10/24 (With Transcriptions)

2 Comments. Leave new

  • 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
  • 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

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