How to Overcome the SQL Server Filtered Index UnMatchedIndexes Issue

By: Ahmad Yaseen   |   Updated: 2017-09-26   |   Comments   |   Related: > Indexing

Problem

SQL Server Filtered Indexes, introduced in SQL Server 2008, are a special type of non-clustered index.  Filtered Indexes provide us the ability to define a filter predicate in the WHERE clause while creating the index in order to target certain data values that satisfy the predicate criteria of queries in an application resulting a smaller version of the index. In most cases, the filtered index is a very useful solution for performance issues. But in some scenarios, the SQL Server Query Optimizer decides not to use the filtered index. So, what is the reason behind that? And how could we overcome that issue?

Solution

In the normal cases, the SQL Server Query Optimizer decides to use the most useful index for the submitted query, which is faster than scanning the underlying table. And it will be faster when the query fits a filtered index, due to its small size. But this scenario is not what we face all the time. Filtered indexes do not always work well with parameterized queries.

When compiling a parameterized query, the SQL Server Query Optimizer cannot predicate the parameter value to build the query plan.  Since the filtered index doesn’t contain all possible values for the column, due to the defined filter, the SQL Server Query Optimizer will not use that filtered index in the parameterized query plan to retrieve the data.  In most cases, this will cause a performance degradation due to the extra time and resources consumed to scan the table or the clustered index.

Let us understand how the SQL Server Query Optimizer will behave practically. We will create a new table, define a non-clustered filtered index on that table to cover our query, with a predicate on the employees EmploymentData column to filter the data required in that index using the WHERE clause, and finally fill that table with 4000 records. Please reference the T-SQL script below:

USE MSSQLTipsDemo
GO

CREATE TABLE FilteredIndexWIthParameters
( ID INT IDENTITY (1,1) PRIMARY KEY,
  EMPName NVARCHAR(50),
  EmploymentDate SMALLDATETIME,
  EMPStatus BIT
)

CREATE NONCLUSTERED INDEX IX_FilteredIndexWIthParameters 
               ON FilteredIndexWIthParameters (EmploymentDate)
               INCLUDE (EMPName,EMPStatus)
               WHERE EmploymentDate>'2011-01-01'

INSERT INTO FilteredIndexWIthParameters VALUES ('AAA','2011-01-15',1)
GO 1000
INSERT INTO FilteredIndexWIthParameters VALUES ('BBB','2007-08-13',0)
GO 1000
INSERT INTO FilteredIndexWIthParameters VALUES ('CCC','2016-05-10',0)
GO 1000
INSERT INTO FilteredIndexWIthParameters VALUES ('DDD','2008-01-07',1)
GO 1000   

Now the table is ready. If we try to retrieve the records of all employees employed in May 2014 and later, using the SELECT statement below:

SELECT * FROM FilteredIndexWIthParameters  WHERE EmploymentDate >= '2014-05-01'   

And check the execution plan for that query, we will see that an Index Seek operation will be performed on the filtered index. Which means that the SQL Server Query Optimizer benefits from the filtered index as the requested EmploymentDate values are included within that filtered index definition, recalling that the filtered index covers all employees employed after 1/1/2011. The execution plan in our case will be:

But, if we try to retrieve the records of all employees employed after May 2010, using the SELECT statement below:

SELECT * FROM FilteredIndexWIthParameters  WHERE EmploymentDate >= '2010-05-01'   

You will see from the generated execution plan for that query, that a Clustered Index Scan operation will be performed on the table’s clustered index. This means that the SQL Server Query Optimizer doesn’t consider the filtered index in the query plan, due to the fact that the requested EmploymentDate values are not included within that filtered index definition, as the filtered index covers only the employees employed after 1/1/2011. The execution plan in this case will be:

In the previous query, what prevents the SQL Server Query Optimizer from considering the filtered index in the query plan is the predicate value that is not located in the filtered index data range. Let us rewrite the first query so that it matches the filtered index by passing the EmploymentDate value as parameter using the T-SQL script below:

DECLARE @Date varchar(20)='2014-05-01'
SELECT * FROM FilteredIndexWIthParameters  
WHERE EmploymentDate >= @Date   

What will shock us now is that SQL Server Query Optimizer will generate a worse plan that will scan the table’s clustered index and take longer time without considering the filtered index in the query plan, although the date value provided fits the filtered index predicate as shown below:

The main reason behind the difference in query plans is the use of parameters in the query. Recall that for parameterized queries, the SQL Server Query Optimizer should create a plan that fits all column values, including the EmploymentDate values falling outside the filtered index data range. And the parameter is open, where you are able to pass any date value to it locating inside or outside the filtered index data range. This is why the SQL Server Query Optimizer didn’t consider that filtered index in the query plan, as it will not be optimized for all the parameter values.

You can see also from the execution plan the waning sign appeared in the left most SELECT operator, the UnmatchedIndexes warning. This warning will be displayed to tell you that the query performance will be better if you don’t use the parameters, as the filtered index will match in this case. This warning can be checked from the SELECT operator properties window, in the Warnings section as shown below:

This warning also appears in the query XML plan as an attribute in the Warning element shown below:

So, how could we overcome that issue? The first solution that we may think about is enforcing the filtered index usage within the query using the WITH(INDEX(…)) table hint as below:

DECLARE @Date varchar(20)='2014-05-01'
SELECT * FROM FilteredIndexWIthParameters WITH (INDEX(IX_FilteredIndexWIthParameters))
WHERE EmploymentDate >= @Date   

Unfortunately, it will fail with the error shown below:

A better solution is forcing the SQL Server Query Optimizer to evaluate which index will fit the query each time the query is executed based on the actual parameter value passed in the query. This can be achieved by adding the Option(Recompile) query hint at the end of our query as in the T-SQL query below:

DECLARE @Date varchar(20)='2014-05-01'
SELECT * FROM FilteredIndexWIthParameters
WHERE EmploymentDate >= @Date
OPTION (RECOMPILE)   

It is really nice. The Query Optimizer evaluates the passed date value and detects if the parameter value falls within the filtered index data range. So it considered using the filtered index in the query plan as shown below:

If you try to pass a date value that falls outside the filtered index data range to the parameter as in the query below:

SET  STATISTICS TIME ON 
DECLARE @Date varchar(20)='2010-05-01'
SELECT * FROM FilteredIndexWIthParameters
WHERE EmploymentDate >= @Date
OPTION (RECOMPILE);   

The Query Optimizer will evaluate the passed value again and find that the filtered index will not fit that value, so it will not consider using the filtered index in the query plan and scan the clustered index instead, as shown below:

Another way to overcome the filtered index UnMatchedIndexes issue in the parameterized queries is explicitly including the filtered index predicate in your query predicate. In this way, you are explicitly telling the Query Optimizer that the filtered index fits your query:

DECLARE @Date varchar(20)='2014-05-01'
SELECT * FROM FilteredIndexWIthParameters
WHERE EmploymentDate >= @Date AND EmploymentDate>'2011-01-01'   

The Query Optimizer decides to use the filtered index as shown in the generated execution plan below:

The last way that can be used to fit between the two enemies; the filtered index and the parameterized queries, is executing the query as a dynamic query. If we rewrite the previous query to be a dynamic query as in the T-SQL script below:

DECLARE @SQLQuery NVARCHAR(2000)
DECLARE @Date varchar(20)='2014-05-01'
SET @SQLQuery= 'SELECT * FROM FilteredIndexWIthParameters
WHERE EmploymentDate >='''+ Cast(@Date AS VARCHAR(20)) + '''';
EXEC sp_executeSQL @SQLQuery;   

You can see clearly from the query execution plan, that the trick succeeded with the Query Optimizer, as the parameterized dynamic query will be executed as a normal non-parameterized query, that will consider the filtered index in the query plan due to using a date value falling within the filtered index data range, as shown in the execution plan below:

Next Steps

• Check SQL Server Filtered Indexes What They Are, How to Use and Performance Advantages.
• Check also Using Parameters for SQL Server Queries and Stored Procedures.
• Read more about SQL Server Indexing Tips.
• Learn about Different Approaches to Correct SQL Server Parameter Sniffing.

About the author

Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

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

View all my tips