Fixing some of the problems caused when you introduce rowstore filtered indexes

Reading Time: 3 minutes

In this post I want to cover fixing some of the problems caused when you introduce rowstore filtered indexes. Because issues can occur.

In addition, in a past post here I did an overview of different index types. I said in that post that I think filtered indexes could be more popular.

Some of you have probably been there before. You’ve put in your first filtered index on a database only to find an issue has happened. I’ve witnessed these issues at a few places. With this in mind, this post will hopefully reduce the pain.

By the end of this post, you will know how to fix some of the problems caused when you first introduce rowstore filtered indexes to a SQL Server database.

Script or stored procedure stops working

So, you’ve added a filtered index and then all of a sudden, a script or stored procedure doesn’t work. Don’t panic. It’s due to the fact filtered indexes need certain SET options set to work properly. Now, you have a couple of options here.

Either alter the script or stored procedure so it uses the right SET option. Or alternatively drop the filtered index before you run the script and recreate it again afterwards.

If you do decide to change the script to use the right SET option, make sure you test the results to make sure they are still correct. For the list of the right SET options go to here.

CheckDB is very slow

This is another common issue when you introduce a filtered index. You add it and suddenly your maintenance jobs slow right down. After you look into it you discover DBCC CheckDB is now taking even longer then usual. This also happens if you add indexes that use computed columns.

I found out these were issues thanks to Paul Randal and Argenis Fernandez in this post.

In SQL Server 2016 Microsoft made changes to reduce the chances of this being an issue. However, if you are still using an older version the best thing to do is to generate a script to disable all filtered indexes and indexes that use computed columns before CheckDB runs.

Make sure you have another script in place that rebuild all these disabled indexes once CheckDB has completed.

I’ve seen this change a CheckDB that was taking over 23 hours for one large database to 17 hours for all the databases on that instance.

Filtered index isn’t being selected

Another common thing I hear people say is that their filtered index isn’t being selected. A lot of the times it’s because they don’t realise that the ‘where’ clause for the filtered index must match exactly the query that you want it to work with.

Sometimes it’s due to the filtered index not having the same columns as the query you are trying to improve.

However, sometimes it’s just because SQL thinks another index will do a better job, and it’s usually right. Your filtered index maybe too inefficient. It might not be using Included columns efficient enough.

You can find yourself in a situation where you need to be even more selective with a table. If this is the case, you might need to consider using indexed views. This is where knowing execution plans will be to your advantage.

Further reading

There are a lot of resources in books and online as to why your rowstore indexes might not be selected. If you are having problems, I suggest you look and discover why.

I highly recommend looking for the material about indexes by well known experts like Kalen Delaney and Kimberly Tripp for further insights.

Final word

Anyway, that’s a quick round up of some of the more common issues when introducing rowstore filtered indexes. Feel free to reach out with comment below.