What is the effect of replacing indexes with filtered (non-null value) indexes?

Question

Our project runs a very large, very complicated database. So about a month ago, we noticed that the space used by indexed columns containing null values was getting too large. As a response to that, I wrote as script that would dynamically search through all single-column indexes containing more than 1% of null values, then drop and recreate those indexes as filtered indexes on the condition that the value was NOT NULL. This would drop and recreate hundreds of indexes throughout the database and typically free up almost 15% of the space used by the whole DB.

Now I have two questions about this:

A) What are the downsides of using filtered indexes in this fashion? I would assume that it would only improve performance, but are there any performance risks involved?

B) We received errors ('can't drop index XYZ beause it does not exist or you do not have permission') on dropping and recreating the indexes, even though when checked afterwards, everything had gone exactly as expected. How can this happen?

Thanks for any help!

Edit: In response to @Thomas Kejser

Hi and thanks, but it turns out this was a disaster. At the time we didn't understand several things such as:

1. During a query, SQLOS makes index plans prior to determining that it cannot use NULL values for joining table columns. IE, you truly do need to have a WHERE clause filter fitting the index for each and every filtered index used in the query, or the index will not be used at all.
2. Dropping and creating indexes and redundantly updating their statistics yet again afterwards still may not be enough to produce the updated plans, which we assumed they would. It appears in some cases only a high enough workload will force SQL Server to reassess the plans.
3. There are some exotics to the functionality of the execution planner that are difficult to determine by common sense and logic alone. With thousands of code-behind -generated variations of different queries even, seemingly useless indexes can help in some statistics and query plans that end up being used in critical queries.

In the end, these changes were reverted. So filtered indexes are a powerful tool, but you need to truly understand exactly what data is being fetched from those columns. Where normal indexes aside from the space issues are rather easy to apply, filtered indexes represent very customized solutions. They are certainly not a replacement for a regular index, rather an extension to them in those special circumstances they're required.

Answer 1

Very interesting approach. My upvote for the creativity.

Since you reclaimed the space, I assume the original indexes are no longer in place? The downsides of filtered indexes then are:

• Too many of them may cause the search space of the optimiser to grow too large, leading to poor query plans as the optimiser times out
• There are several situations where a filtered index will not even be considered, even though the non-filtered equivalent would be. Notably, this can happen when you get a hash join on the indexed column or if you try to ORDER BY the column (without a filter)
• Query parameterisation doesn't work with filtered indexes (see: http://www.sqlservercentral.com/blogs/practicalsqldba/2013/04/08/sql-server-part-9-filtered-index-a-new-way-for-performance-improvemnt/)

In practical terms, this means that you have to be extremely careful with filtered indexes as they will often result in horrible query plans. I would not go so far as to call them useless, but I view them as an addition to traditional indexes, not as a replacement (as you are trying to do).

Answer 2

Thomas Kejser answer this topic well above.

I just thought about adding 2 cents.

I have seen some filtered indexes only being used (shown in the execution plan) when you exact match the where clause in your query as the where in the filtered index.

have you tried to use indexed views? sparse columns?

I believe that as far as you have only inner joints you can create an indexed view containing the where clause(s) of your filtered indexes and then you could use the view instead.

There could be more than one view. But same as with the non clustered indexes, too many will slow your writing down.

In my experience you would have good gains in reading but you would have to monitor writes (inserts and updates) specially if the tables are involved in replication.

However, as I understand your main concern are the null values therefore I would suggest you SPARSE columns in your indexes.

Sparse columns are especially appropriate for filtered indexes

As I have advertised sparse columns I would not feel well if I didnt tell you about its limitations too:

When designing tables with sparse columns, keep in mind that an additional 2 bytes of overhead are required for each non-null sparse column in the table when a row is being updated.

As a result of this

additional memory requirement, updates can fail unexpectedly with error 576 when the total row size, including this memory overhead, exceeds 8019,

and no columns can be pushed off the row.

Consider the > example of a table that has 600 sparse columns of type bigint.

If there are 571 non-null columns, then the total size on disk is 571 * 12 = 6852 bytes. After including additional row overhead and the sparse column header, this increases to around 6895 bytes. The page still has around 1124 bytes available on disk. This can give the impression that additional columns can be updated successfully. However, during the update, there is additional overhead in memory which is 2*(number of non-null sparse columns). In this example, including the additional overhead – 2 * 571 = 1142 bytes – increases the row size on disk to around 8037 bytes. This size exceeds the maximum allowed size of 8019 bytes. Since all the columns are fixed-length data types, they cannot be pushed off the row. As a result, the update fails with the 576 error.

more details on the link above, however I prefer to post here this warning also:

Changing a column from sparse to nonsparse or nonsparse to sparse requires changing the storage format of the column.

The SQL Server Database Engine uses the following procedure to accomplish this change:

1 - Adds a new column to the table in the new storage size and format.

2 - For each row in the table, updates and copies the value stored in the old column to the new column.

3 - Removes the old column from the table schema.

4 - Rebuilds the table (if there is no clustered index) or rebuilds the clustered index to reclaim space used by the old column.