SQL Server Filtered Index Essentials Guide

By: Simon Liew   |   Updated: 2024-04-03   |   Comments   |   Related: > Indexing

Problem

Filtered indexes can significantly boost query performance, but simple mistakes can hinder its usage. This tip will help you understand how to use SQL Server filtered indexes and identify queries that could benefit from them that aren't currently doing so.

Solution

Filtered indexes are regular non-clustered indexes that only contain a subset of data (filtered data). Filtered indexes are especially useful for narrow query coverage that requires fast retrieval and high accessibility.

The key for the SQL Server Optimizer to properly utilize filtered indexes are:

1. Ensure the query predicate(s) are equivalent to the filtered index expression. Sometimes, the predicate does not have to be an exact match, and the SQL Server Optimizer can determine this. However, simplicity is still the best.
2. The query predicate(s) on the filtered index column(s) are not parameterized or using variable assignment.

When the SQL Server Optimizer encounters the scenario in the second point above, the root node of the query plan shows an UnmatchedIndex warning at the root of the query (which we will show below). This warning means the SQL Server Optimizer could potentially benefit from the filtered index but chose not to use it.

Demonstration of Filtered Index

It is easier to show the problem and solution with a demonstration. For this demo I used SQL Server 2019.

USE tempdb
GO

SET NOCOUNT ON;

DROP TABLE IF EXISTS dbo.TestFilteredIndex;

CREATE TABLE dbo.TestFilteredIndex (
[Idx] INT IDENTITY(1,1), 
[Descr] VARCHAR(30) DEFAULT 'MSSQLTips Filtered index demo', 
[Status] TINYINT,
CHECK ([Status] IN (1,2,3)),
PRIMARY KEY (Idx));
GO

DROP INDEX IF EXISTS IX_TestFilteredIndex_Status_Fil ON dbo.TestFilteredIndex;
CREATE INDEX IX_TestFilteredIndex_Status_Fil ON dbo.TestFilteredIndex ([Status]) WHERE [Status] = 2;
GO

INSERT dbo.TestFilteredIndex ([Status]) values (1);
GO 8800

INSERT dbo.TestFilteredIndex ([Status]) values (2);
GO 11

INSERT dbo.TestFilteredIndex ([Status]) values (3);
GO 11000

In the script above, we are creating a filtered index on rows with [Status] = 2 to reflect this requirement.

Before executing the query below, enable Include Actual Execution Plan (Ctrl + M) in SSMS, and then run the query.

SELECT [Descr], [Status] FROM dbo.TestFilteredIndex WHERE 1 = 1 AND [Status] = 2;
GO

The query plan above shows an equality predicate filtering on a constant value of 2 (see red boxes), which matches the filtered index expression, and our filtered index is used as expected.

On a side note, if the query output always retrieves the [Descr] column, having [Descr] as an included column in the filtered index definition would make sense. We will ignore this requirement in this tip.

Parameterizing Query and Filtered Indexes

The next step is when most developers would parameterize the data retrieval, typically with a stored procedure. For simplicity in our demonstration, we will parameterize and execute the ad-hoc query assigning the value into a variable as below.

DECLARE @Status TINYINT = 2;
SELECT [Descr], [Status] FROM dbo.TestFilteredIndex WHERE 1 = 1 AND [Status] = @Status;
GO

The query plan has changed, and the filtered index is not being utilized. Instead, a clustered index scan occurs on the [dbo].[TestFilteredIndex] table. All we have done is parameterized the input value of the [Status] column.

We could try to force the index hint as shown below to see if this works.

DECLARE @Status TINYINT = 2;
SELECT [Descr], [Status] FROM dbo.TestFilteredIndex WITH (INDEX(IX_TestFilteredIndex_Status_Fil))
WHERE 1 = 1 AND [Status] = @Status;
GO

As you can see, we get an error if we try to do this.

Query Optimizer Dilemma

What you see is not what the SQL Server Optimizer sees - the optimizer cannot employ the filtered index if the predicate is a parameter variable during the optimization phase. A query plan is generated during compile time using the value assigned to it when it is first executed. This plan is then cached, and subsequent executions of the same query will use this cached query plan regardless of any other distinct value @Status being passed in. So, the SQL Server behavior to perform a table scan is perfectly valid.

In a hypothetical scenario where SQL Server uses the filtered index, and the runtime of @Status is 1, this would then yield an incorrect output given the absence of rows with [Status] = 1 in the filtered index.

Workaround for Filtered Indexes

The crux of the workaround lies in allowing SQL Server to sniff or know the passed-in value, deeming it safe to use the filtered index. One workaround is to use dynamic T-SQL and concatenate the @Status value into the query.

Another simple workaround, though it might not be recommended, is to use option recompile on the query. If you are looking at the Display Estimated Execution Plan on the query below, the warning will still show. This is because the SQL Server Optimizer still uses the @Status variable when generating the estimated query plan.

DECLARE @Status TINYINT = 2;
SELECT [Descr], [Status] FROM dbo.TestFilteredIndex WHERE 1 = 1 AND [Status] = @Status
OPTION (RECOMPILE);
GO

You should not add the option recompile to a query without a comprehensive understanding of its impact on both behavior and performance. The recompilation triggers parameter embedding optimization, enabling the SQL Server Optimizer to derive the actual parameter value. This makes practical sense, if the SQL Server Optimizer needed to do all the hard work of generating a new query plan on every execution, it might as well derive the actual parameter value to get better cardinality and determine the proper index path to be used since the generated query plan here is solely for this specific parameter.

UnmatchedIndexes

If you look closely at the query plan of the parameterized query, there is a small warning sign at the root of the query plan (underlined below). If you check the properties, you will see UnmatchedIndexes. This warning suggests that a query could potentially benefit from a filtered index but is currently unable to leverage it.

The query below can be used to identify possible unused filtered indexes due to parameterization in your SQL Server cache. Beware of executing this on a SQL Server instance that has a large amount of memory, as it can take up resources to process a large cache.

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SELECT  
    st.text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qp.query_plan.value('count(//p:UnmatchedIndexes)', 'int') > 0 
ORDER BY total_worker_time DESC;
GO

The query text and query plan output here would be a good starting point for analyzing queries with UnmatchedIndexes.

An observation if you read this tip carefully is the usage of 1 = 1 in the query WHERE clause. If you remove the 1 = 1 condition from the query, you will see the UnmatchedIndexes warning even though the query uses the value 2 in the predicate. This is because SQL Server auto-parameterizes the query with @1, but it also recognizes the actual value passed in during run-time and uses the filtered index, albeit the warning.

SELECT [Descr], [Status] FROM dbo.TestFilteredIndex WHERE [Status] = 2;
GO

As a final step, remember to clean up our test table.

USE tempdb
GO
DROP TABLE IF EXISTS dbo.TestFilteredIndex;
GO

Conclusion

Developers often choose to use variable predicates in queries for various good reasons. However, caution is warranted in specific scenarios, especially when employing a filtered index tailored for a particular use case. Be mindful of potential tips and traps in such situations.

Additionally, it is a good practice to have a structured index naming convention to distinguish different types of indexes, especially filtered indexes.

Next Steps

• Create filtered indexes
• SQL Server Filtered Indexes What They Are, How to Use and Performance Advantages

About the author

Simon Liew is an independent SQL Server Consultant in Sydney, Australia. He is a Microsoft Certified Master for SQL Server 2008 and holds a Master’s Degree in Distributed Computing.

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

View all my tips