Filtered Indexes Ignored in SQL Server

Problem

Isn't it frustrating to spend hours creating filtered indexes just for the query optimizer to ignore them? You followed all the standard guidelines. The filtered index only contains a small portion of the overall rows. You made sure that the WHERE clause is correct. So, what gives? There is another simple reason SQL Server ignores a filtered index you don't see talked about much.

Solution

In this article, we'll start by looking at what a filtered index is and when you would want to use one. Also, I'll look at the three primary benefits of incorporating them into your environment. Continuing, we'll look at some typical reasons SQL Server cannot use them. Additionally, we'll uncover a less talked about reason SQL Server ignores your filtered indexes. Finally, I'll recommend a method to encourage SQL Server to pick them now and in the future. We have a lot of great information to cover, so let's get started.

What is a SQL Server Filtered Index?

I like to describe an index as a small copy of the table since you remove columns. At least, I hope you don't include every column in your indexes. To take it one step further, filtered indexes remove rows. For clarity, you can only have nonclustered filtered indexes. However, you can create unique filtered indexes. Microsoft introduced filtered indexes in SQL Server 2005 nearly two decades ago, but I rarely see them in the wild. The syntax below represents how to create one. It's basically the same as a standard nonclustered, but you add a WHERE clause.

CREATE NONCLUSTERED INDEX [IX_MySuperFilteredIndex]
ON BigTable (
                Column1,
                Column2
            )
WHERE Column3 = 'Some Value';

Common Use Cases

Below are some of the most common use cases for filtered indexes. There are more, but these give you a nice sampling.

Hot data: When you have data that are frequently accessed. Think of a table with open sales orders; users will likely access them more often than closed ones.
Soft deletes: If instead of deleting a row, you have a column indicating it's active or deleted. Chances are you don't want to include these rows in most query results.
NULL placeholders: When most of your queries exclude NULLs for specific columns, you might benefit from excluding them from the index.
Recent dates: Chances are you access more recent than historical data. For example, if you only return sales from the current year or month. Unfortunately, you can't use a GETDATE() function in a filtered index. Meaning this would need to be managed manually.

Advantages of SQL Server Filtered Indexes

On its website, Microsoft touts three primary benefits of filtered indexes.

Improved query performance and plan quality: This might differ from what you expect. The performance gains mainly come through better statistics. Naturally, the statistics are more accurate on a filtered index than on a full table.
Reduced index maintenance cost: The filtered index is smaller, making reorganization and rebuilding more efficient.
Reduced index storage cost: This one, in my opinion, provides the largest benefit. A filtered index can be a fraction of the size of a non-filtered one. A smaller footprint means you can create multiple filtered indexes without wasting storage.

Build Your Dataset

To get started, let's create a large table with a column screaming for a filtered index.

USE [master];
GO

IF DATABASEPROPERTYEX ('FilteredIndexes','Version') IS NOT NULL
BEGIN
  ALTER DATABASE FilteredIndexes SET SINGLE_USER
  WITH ROLLBACK IMMEDIATE;
  DROP DATABASE FilteredIndexes;
END
GO

CREATE DATABASE FilteredIndexes;
GO

ALTER DATABASE FilteredIndexes SET RECOVERY SIMPLE;
GO

USE FilteredIndexes;
GO

DROP TABLE IF EXISTS dbo.Numbers;
GO

DECLARE @UpperBound INT = 5000000;
;WITH cteN(Number) AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
  FROM sys.all_columns AS s1
  CROSS JOIN sys.all_columns AS s2
)
SELECT [Number] INTO dbo.Numbers
FROM cteN WHERE [Number] <= @UpperBound;


CREATE TABLE dbo.ProductLaunch
(
    Id INT IDENTITY(1,1) NOT NULL,
    Amount DECIMAL(36, 2) NOT NULL,
    [Status] TINYINT NOT NULL,
    [Description] NVARCHAR(250) NULL,
   LaunchDate DATE NULL,
   CONSTRAINT [PK_ProductLaunch_Id]
        PRIMARY KEY CLUSTERED (Id)
);

DECLARE @StartDate AS DATE;
DECLARE @EndDate AS DATE;
DECLARE @DaysBetween AS INT;

SELECT @StartDate = '01/01/2023',
       @EndDate = '02/28/2023',
      @DaysBetween = (1 + DATEDIFF(DAY, @StartDate, @EndDate));

INSERT INTO dbo.ProductLaunch
(
   Amount,
    [Status],
    [Description],
    LaunchDate
)
SELECT TOP (5000000)
      (ABS(CHECKSUM(NEWID()) % 1000) + 1) AS Amount,
       (ABS(CHECKSUM(NEWID()) % 10) + 1) AS [Status],
       'The dude abides.' AS [Description],
       CASE
           WHEN (n.Number % 150) = 0 THEN
               DATEADD(DAY, RAND(CHECKSUM(NEWID())) * @DaysBetween, @StartDate)
           ELSE
               NULL
       END AS LaunchDate
FROM dbo.Numbers n;
GO

The syntax above creates one table with five million rows.

Create Your SQL Server Filtered Index

Suppose the business runs a version of the following query millions of times daily. When you run the query and look at the execution plan, does the green hint suggest a filtered index? No, and it never will. If you're into the Database Tuning Advisor (DTA), it sometimes will.

SELECT LaunchDate,
       COUNT(Id) AS CountByDay,
       SUM(Amount) AS MoneyMade
FROM dbo.ProductLaunch
WHERE LaunchDate IS NOT NULL
GROUP BY LaunchDate;

Now let's create a filtered index for the query above and rerun to see if SQL chooses our new index.

CREATE NONCLUSTERED INDEX [IXFiltered_LaunchDate_Amount]
ON dbo.ProductLaunch (
                         Id,
                         Amount
                     )
INCLUDE (LaunchDate)
WHERE LaunchDate IS NOT NULL;

Mission accomplished! SQL made a wise decision and picked the filtered index. Hold on, don't get too excited.

SQL Server Filtered Indexes Ignored

Several reasons explain why the optimizer won't pick a filtered index. For example, if your data isn't filtered enough. Let's say half the data in your table is part of the filtered index, don't hold your breath on SQL picking it. Additionally, operator and syntax constructs discourage the optimizer from picking the index. Brent Ozar has an easy-to-read post covering several limits in detail.

Let's look at one you don't see talked about much. We'll create a covering nonfiltered index, rerun our query, and see which one SQL chooses.

CREATE NONCLUSTERED INDEX [IXNonFiltered_LaunchDate_Amount]
ON dbo.ProductLaunch (
                         LaunchDate,
                         Id,
                         Amount
                     )

What gives SQL? Why did you choose the much larger nonfiltered index? The answer's simple, SQL Server likes to play it safe. I don't blame it. I like having an illusion of safety too. Most if not all of the time, SQL chooses the covering nonfiltered index. This fact, my friend, is the hard truth of why the optimizer frequently ignores the smaller filtered index.

Moving Forward

What workarounds exist? There isn't one other than to not create a nonfiltered index covering the same query. You could force it with an index hint, but that often comes back to bite me. However, from now on, include almost any column you think SQL needs. Here's the thing, the filtered index is tiny compared to the other one. Next month when the query below is the most popular, you'll be ready.

SELECT LaunchDate,
       COUNT(Id) AS CountByDay,
       SUM(Amount) AS MoneyMade,
       [Status]
FROM dbo.ProductLaunch
WHERE LaunchDate IS NOT NULL
GROUP BY LaunchDate,[Status];

Notice that I added the status column in the query above. Take a guess which column I'll add the index to.

CREATE NONCLUSTERED INDEX [IXFiltered_LaunchDate_Amount]
ON dbo.ProductLaunch (
                         Id,
                         Amount,
                         [Status]
                     )
INCLUDE (LaunchDate)
WHERE LaunchDate IS NOT NULL
WITH (DROP_EXISTING = ON);

The optimizer started using the filtered index again.

Validating Your SQL Server Filtered Index

Several ways exist to validate if SQL uses your tiny, filtered index. The first would be looking at the execution plan. However, the execution plan may not be a direct reflection of Production. All the fun happens out in Production. The other is using the Dynamic Management views sys.dm_db_index_usage_stats. I've included a script below that I borrowed from Greg Robidoux to generate the data. Keep in mind that SQL clears out the views anytime you restart the service. Please don't run this once and decide to drop all your unused indexes.

SELECT DB_NAME(DATABASE_ID) AS DATABASENAME,
       SCHEMA_NAME(C.SCHEMA_id) AS SCHEMANAME,
       OBJECT_NAME(B.OBJECT_ID) AS TABLENAME,
       INDEX_NAME = (SELECT NAME 
                     FROM   SYS.INDEXES A 
                     WHERE  A.OBJECT_ID = B.OBJECT_ID
                       AND  A.INDEX_ID = B.INDEX_ID),
       USER_SEEKS,
       USER_SCANS,
       USER_LOOKUPS,
       USER_UPDATES
FROM   SYS.DM_DB_INDEX_USAGE_STATS B
       INNER JOIN SYS.OBJECTS C ON B.OBJECT_ID = C.OBJECT_ID
WHERE  DATABASE_ID = DB_ID(DB_NAME())
       AND C.TYPE = 'U';

It's easy to see from the screenshot above that SQL used our filtered index. Please let me know your experiences with filtered indexes in the comments below. Do you have trouble with SQL putting them to good use?

Key Takeaways for Filtered Indexes

Filtered indexes can be a huge space saver, which means smaller backups. Not to mention faster maintenance when rebuilding or reorganizing. Statistics also update faster.
There are several reasons why SQL cannot use a filtered index. Including certain operators in the WHERE clause. For a detailed list, check out this link from Microsoft.
An often-understated reason is an existing unfiltered covering index. SQL Server likes to keep it safe. If you think about it, can you blame SQL Server? It costs the same IO to read a filtered versus nonfiltered index.
Consider over-covering to encourage the continued use of your filtered index.
Check production and make sure your filtered indexes are not just adding maintenance overhead.

Next Steps

Greg Robidoux wrote a detailed article on determining if unused indexes exist in your environment: Deeper insight into used and unused indexes for SQL Server.
Would you like to learn more about filtered indexes? Eric Blinn wrote an informative article on specific use cases for implementing them.
Was your interest sparked when I mentioned the Database Tuning Advisor above? If you want to learn more, please check out this article from Diogo Souza.
Read my previous tip on Evaluating SQL Server Filtered Indexes in a Multi Table SQL Join.
Learn about SQL Server Filtered Indexes What They Are, How to Use and Performance Advantages.
Check out all of the SQL Server Indexing Tips on MSSQLTips.com

About the author

Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for MSSQLTips.com, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.

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

View all my tips

Article Last Updated: 2023-02-16

Overlooked Reason SQL Server Ignores Your Filtered Indexes