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.

  • You may want to re-examine your indexing strategy too. If you have hundreds of single field indexes it's probably not optimal. – JNK Jan 29 '13 at 13:46
  • The need for these comes from the fact that the database is partially inherited from another system. By default, we have some abstract tables, and several abstract columns that may not be used at all, which produces most of these massive amounts of indexed NULL values. As for the single-field indexes, they're created from the base requirement that each foreign key should be indexed, and many of those are in these columns that contain mostly or only NULL values. – Kahn Jan 29 '13 at 14:35

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:

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).

  • "Query parameterisation doesn't work with filtered indexes" . this can probably be fixed with option(recompile) – MichaelD Jan 10 '14 at 8:32

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.

  • 1
    Hi. A bit late to the fray but yes, while we abandoned the approach described in this topic a long time ago, we did recently come back to it with a more selective approach. Basically, we looked at the statistics usage and business model to confirm the indexes on a table per table basis. Then tested it by adding a new filtered index on the side of the normal one, and checked to see over a few weeks which one ended up being used. After confirming that ONLY the filtered indexes were used in new plans, we dropped the normal nonfiltered ones. – Kahn Oct 5 '15 at 8:25
  • 1
    Also, we did change quite a few columns to sparse types. The problem with that however is that as you'll see from MSDN, altering a column type to sparse basically forces the whole clustered index to be recreated. Making this rather heavy for large, complex tables. So we renamed the constraints and the table, created a new one with the same model and original name but with sparse columns, and then transferred the data into the new table in appropriate batches. Then once checked that everything was ok and all the indexes and FK's were again in place, dropped the old tables. – Kahn Oct 5 '15 at 8:27
  • 1
    Also, in some cases using page compression was far preferable, so we ended up doing that instead. It's also handy since you can simply create the existing clustered index with DROP_EXISTING = ON, to make it far, far faster than going the sparse route. Especially since it avoids the whole hassle of re-managing indexes and FK's. – Kahn Oct 5 '15 at 8:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.