How Forced Parameterization in SQL Server Affects Filtered Indexes

By: Aaron Bertrand | Updated: 2019-09-19 | Comments | Related: > Indexing

Problem

Forced parameterization is a valuable feature in SQL Server, reducing plan cache bloat by not introducing a new plan for every single combination of predicate literals. If you read Brady Upton’s tip for the fundamental differences between simple and forced parameterization, he touches on one area where forced can steer you wrong: the same plan is reused, even when a different parameter value should have yielded a different plan due to data skew. But there is another potential cost involved: if you use filtered indexes, those indexes could be rendered useless.

Solution

A filtered index can be very handy for particular types of queries that use predicates against known values (though I will concede they are not perfect). You might have a filtered index only on library books marked overdue or customers that are inactive, or temporary filtered indexes built for specific business periods with high query frequency (think a flower shop the week before Mother’s Day). The filtered index is beneficial since it doesn’t have to store an index row for every row in the base table – it only cares about those rows that match the filter. If the library has 500,000 books, but only 200 are marked overdue, the index only has to maintain those 200 rows, so reading that index is going to be much more efficient than dealing with all the rows in the table.

Let’s take a simple example where we have a bunch of potential status values – 0, 1, 2, etc. But then there is a special case, when a row is marked as status = 255, this is something that only happens rarely, but we are querying for those rows a lot, so we’re going to create a filtered index.

CREATE DATABASE aw;
GO
USE aw;
GO

CREATE TABLE dbo.what
(
  id int NOT NULL IDENTITY(1,1), name sysname, status tinyint,
  CONSTRAINT PK_what PRIMARY KEY(id)
);

-- distribute status values 0, 1, 2 (for me, ~11K rows, YMMV):
INSERT dbo.what(name,status)
 SELECT name, ABS(object_id) % 3 FROM sys.all_columns;

-- update ~11 rows to a different status:
UPDATE dbo.what SET status=255 WHERE id % 1000 = 0;

-- create a filtered index:
CREATE INDEX filtered ON dbo.what(id,status) INCLUDE(name) WHERE status = 255;

Now observe that the filtered index is used for this query:

SELECT * FROM dbo.what WHERE status = 255;

Results:

Filtered index scan operator under simple parameterization

I’ve manually highlighted the important points in orange. Even though the statement appears to have been parameterized, the query plan was still produced with the intent to use the filtered index. Notice that the actual rows and estimated rows were very low (even though estimated were not accurate), and that the expected I/O cost of this scan is negligible. There is a warning on the select operator about an unmatched filtered index, even though that is clearly the index that was chosen.

Now, what happens when we change the database to use forced parameterization? Someone might do this, in order to stop ad hoc queries with string literals from filling up the plan cache, completely independent of any knowledge of filtered indexes.

ALTER DATABASE aw SET PARAMETERIZATION FORCED;

This change requires exclusive access but does not require a service restart. It will invalidate existing plans, so when you run this query again:

SELECT * FROM dbo.what WHERE status = 255;

The execution plan will look a little different:

Clustered index scan operator under forced parameterization

Again, focusing on the areas highlighted in orange: the statement has a parameter @0 (previously it had @1) but, more importantly, the clustered index is scanned now instead of the filtered index. This has impacts throughout the plan, including how many rows are both estimated to be read and actually read in order to return those 11 rows. You can see a much higher I/O cost (about 22X), the predicate is now listed explicitly in the tooltip, and you can see warnings about residual I/O (which just means a lot more rows were read than necessary). The root operator still has the warning about the unmatched index, so at least the plan gives you some clue that a filtered index exists that might be useful if you change the parameterization setting for the database (or add OPTION (RECOMPILE) to the statement):

That warning is useful but, as noted above, it may be present even when that index is the one that was chosen for the plan.

Summary

Turning on forced parameterization, even though it comes with important upsides, should be tested against any workload, but particularly those that involve filtered indexes. When considering options like parameterization settings to solve other issues, it is important to also check for queries that you expect to benefit from filtered indexes, and make sure you weigh the impact of any change.

Next Steps

Read on for related tips and other resources:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2019-09-19