What About Filtered Indexes?

We would all be happy if tables in our databases only contained the data we need. In reality, we tend to have data that needs kept for legal purposes or “just in case” something happens. Are you querying a table where queries only care about a fraction of the data?

When optimizing a query that is written to return a specific set of data, you may improve performance by creating a filtered index. Let’s look at an example of a filtered index and a warning that you may come across when using it.

Creating a Filtered Index

For our example, let’s say this query will always return the Display and UpVotes count where a Users record has 5000 or more UpVotes:

SELECT DisplayName, UpVotes
FROM Users
WHERE UpVotes >= 5000;
GO

The query results in 44,530 logical reads. We can improve those numbers.

If the UpVotes count in the query can vary, we may want to add an index for the entire table. In our scenario, we only need where UpVotes is >= 5000. We can create a filtered index by adding a WHERE clause to the index creation script:

CREATE INDEX UpVotes_Filtered_Include_DisplayName ON Users
(Upvotes)
INCLUDE (DisplayName)
WHERE UpVotes >= 5000;

Optimizing Optimization

Comparing the index with and without the filter, we see a significant size difference:

In addition to the obvious difference in index size, the smaller index will be much less costly to maintain. Why have the overhead of a 91000 KB index when an index 1/1000 of the size can get the job done?

Without the index we were scanning the PK_Users_Id clustered index to return 921 of 4807 rows:

After our new index, SQL Server chooses to scan our new filtered index for 921 of 921 rows with only 7 logical reads:

Not So Fast

Did your eye catch anything in that last execution plan screenshot? We’re getting a yellow exclamation point. Nothing shows when hovering over the plan:

Let’s right click on the SELECT, choose properties, and check the warnings section:

We have a warning for UnmatchedIndexes. SQL Server is telling us that a filtered index could not be used because of a parameterized query. That seems odd since our query isn’t parameterized. Also, our filtered index was successfully used.

Can’t Stand It

What if you are receiving alerts because of the warning and you want the plan to be warning free? You have some options to get a clean plan. We can add something like “AND 1=1” which will give us a better looking plan:

You could also add OPTION(RECOMPLE) to trick SQL Server and get a clean plan:

If one of these is an easy change to make, go for it. If not, I wouldn’t be concerned.

Thanks for reading!