In this post, we’ll look at how filtered indexes in SQL Server work when the query is parameterised on the column of the filtered index.
The query
The following query in the Stack Overflow 2010 Database, provided under cc-by-sa 4.0 licence from Stack Exchange Data Dump, will find all users with the name John whose account was created prior to 2010. The database is in compatibility level 160 on a SQL Server 2022 instance installed on a VM with 8 cores and 35GB RAM.
SELECT u.id,
u.DisplayName,
u.Location,
p.Title
FROM dbo.Users u
JOIN dbo.Posts p
ON p.OwnerUserId = u.id
WHERE u.CreationDate < '2010-01-01T00:00:00' AND
u.DisplayName = 'John';
If I want to index this query, I could create the following indexes:
CREATE INDEX IX_OwnerUserID ON dbo.Posts
(
OwnerUserID
)
INCLUDE
(
Title
);
CREATE INDEX IX_CreationDate ON dbo.Users
(
CreationDate,
DisplayName
)
INCLUDE
(
Location
);
The optimizer does us a favour and uses our indexes (as well as saying it would like a better index):
There is a warning on the plan, but I will ignore that as it is not relevant to the topic at hand.
The SET STATISTICS IO output tells us that 3153 logical reads were performed:
Table 'Posts'. Scan count 200, logical reads 683, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 2470, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
The following filtered index on the Users table may be more desirable if we run this query a lot, searching for John in particular:
CREATE INDEX IX_CreationDate_JohnFilter ON dbo.Users
(
CreationDate,
DisplayName
)
INCLUDE
(
Location,
AboutMe
)
WHERE DisplayName = 'John';
If I create this index in addition to IX_CreationDate, SQL Server decides to use it when we run our query again:
And the reads on the Users table are vastly reduced from the original:
Table 'Posts'. Scan count 200, logical reads 683, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Users'. Scan count 1, logical reads 4, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
So far, so good.
Stored Procedure
After the query has been in production for a while and has been performing well with our filtered index, we then decide that it would be better to turn this query into a stored procedure so that we can search for users other than John if we so desire (I know, our application is weird!):
CREATE PROCEDURE dbo.spFindPostsByUser
(
@DisplayName NVARCHAR(40),
@CreationDate DATETIME
)
AS
SELECT u.id,
u.DisplayName,
u.Location,
p.Title
FROM dbo.Users u
JOIN dbo.Posts p
ON p.OwnerUserId = u.id
WHERE u.CreationDate < @CreationDate AND
u.DisplayName = @DisplayName;
GO
We then run our stored procedure for John as we did before:
EXEC dbo.spFindPostsByUser @DisplayName = 'John', @CreationDate = '2010-01-01T00:00:00';
Let’s now look at the plan:
Erm… we’ve reverted to using the original, non-filtered index!
OK, let’s change the stored procedure and force our filtered index as we know it’s better, and we like to live dangerously by ordering the optimizer around:
CREATE OR ALTER PROCEDURE dbo.spFindPostsByUser
(
@DisplayName NVARCHAR(40),
@CreationDate DATETIME
)
AS
SELECT u.id,
u.DisplayName,
u.Location,
p.Title
FROM dbo.Users u WITH(INDEX (IX_CreationDate_JohnFilter))
JOIN dbo.Posts p
ON p.OwnerUserId = u.id
WHERE u.CreationDate < @CreationDate AND
u.DisplayName = @DisplayName;
GO
Let’s execute again…
EXEC dbo.spFindPostsByUser @DisplayName = 'John', @CreationDate = '2010-01-01T00:00:00';
This time, we get an error:
Msg 8622, Level 16, State 1, Procedure dbo.spFindPostsByUser, Line 7 [Batch Start Line 0]
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.
Let’s try the hint in the code outside of the stored procedure:
SELECT u.id,
u.DisplayName,
u.Location,
p.Title
FROM dbo.Users u WITH(INDEX (IX_CreationDate_JohnFilter))
JOIN dbo.Posts p
ON p.OwnerUserId = u.id
WHERE u.CreationDate < '2010-01-01T00:00:00' AND
u.DisplayName = 'John';
If we look again at the plan:
This time, it obeys our hint and uses our filtered index – so what’s happening here?
Explanation
The stored procedure plan is built for parameterisation – the optimizer builds a plan that will be valid for any parameter being passed (though not necessarily the most performant)
With this in mind, the plan SQL Server builds for dbo.spFindPostsByUser must be safe for all parameters, i.e. all names whether they be John, Paul, George, Ringo or something else. If we were to pass Paul as a parameter, we cannot use the filtered index as it only contains Johns and therefore SQL Server cannot use the filtered index for a parameterised plan. This is why SQL Server opts to use the “safe” non-filtered index we created.
Some Solutions
There is a way we can get SQL Server to use the filtered index when John is passed as a parameter, which is to add an OPTION (RECOMPILE) hint to the query in the stored procedure:
CREATE OR ALTER PROCEDURE dbo.spFindPostsByUser
(
@DisplayName NVARCHAR(40),
@CreationDate DATETIME
)
AS
SELECT u.id,
u.DisplayName,
u.Location,
p.Title
FROM dbo.Users u
JOIN dbo.Posts p
ON p.OwnerUserId = u.id
WHERE u.CreationDate < @CreationDate AND
u.DisplayName = @DisplayName
OPTION (RECOMPILE);
GO
Now we get the appropriate index depending on the parameter passed:
EXEC dbo.spFindPostsByUser @DisplayName = 'John', @CreationDate = '2010-01-01T00:00:00';
EXEC dbo.spFindPostsByUser @DisplayName = 'Paul', @CreationDate = '2010-01-01T00:00:00';
We can see the John execution uses the filtered index and the Paul execution uses the regular index. The OPTION (RECOMPILE) means SQL Server uses parameter embedding optimization (PEO) to use the values of the parameters in the execution plan as if they were literals. There is a downside to OPTION (RECOMPILE) in that it does recompile the query on each stored procedure execution, which could potentially increase the CPU overhead on busy systems. There is also another way to achieve this which is to use dynamic SQL, as is often the case with this solution, this makes our stored procedure a little more complicated:
CREATE OR ALTER PROCEDURE dbo.spFindPostsByUser
(
@DisplayName NVARCHAR(40),
@CreationDate DATETIME
)
AS
DECLARE @sql NVARCHAR(MAX) = N'
SELECT u.id,
u.DisplayName,
u.Location,
p.Title
FROM dbo.Users u
JOIN dbo.Posts p
ON p.OwnerUserId = u.id
WHERE u.CreationDate < @CreationDate AND
u.DisplayName = ';
IF @DisplayName = N'John'
BEGIN
SET @sql += N'''John''';
EXEC sp_executesql @stmt = @sql, @params = N'@CreationDate DATETIME', @CreationDate = @CreationDate;
END
ELSE
BEGIN
SET @sql += '@DisplayName';
EXEC sp_executesql @stmt = @sql, @params = N'@DisplayName NVARCHAR(40), @CreationDate DATETIME', @DisplayName = @DisplayName, @CreationDate = @CreationDate;
END
GO
Again, SQL Server uses the filtered index on the John execution but not the Paul execution:
EXEC dbo.spFindPostsByUser @DisplayName = 'John', @CreationDate = '2010-01-01T00:00:00';
EXEC dbo.spFindPostsByUser @DisplayName = 'Paul', @CreationDate = '2010-01-01T00:00:00';
This differs from the OPTION (RECOMPILE) version as what we are effectively doing here is caching two plans – one for John, one for everyone else. This lowers the compilation overhead but means we have extra plans in cache.
Conclusion
We’ve looked at how SQL Server will not use filtered indexes if the predicate that would seek the filtered index is a parameter. We looked at a couple of workarounds to this using OPTION (RECOMPILE) and dynamic SQL.
References / Further Reading
dba.stackexchange – Using a filtered index when setting a variable