What is the effect of replacing indexes with filtered (non-null value) indexes?
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).
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.