(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)
Today I want to talk about a specific kind of index in SQL Server: Filtered Indexes, which were introduced back with SQL Server 2008 – so already very long time ago. A Filtered Index is a traditional Non-Clustered Index, which includes a Filter Predicate that restricts the rows that are indexes in that specific index.
Advantages of Filtered Indexes
Let’s start now by looking at the advantages of Filtered Indexes, and where they can be beneficial. Let’s have a look at the column PurchaseOrderNumber of the table Sales.SalesOrderHeader of the AdventureWorks2014 database. As you can see from the following T-SQL query, there are a lot of NULL values in that column. In my case 27659 records out of 31465 records have a NULL value.
SELECT COUNT(*), COUNT(PurchaseOrderNumber) FROM Sales.SalesOrderHeader GO
In the next step I create a Covering Non-Clustered Index for a specific query that I want to speed up.
-- Create a supporting Non-Clustered Index for the query CREATE NONCLUSTERED INDEX idxTest ON Sales.SalesOrderHeader(PurchaseOrderNumber, SalesPersonID) INCLUDE (OrderDate, ShipDate) GO -- When we execute the query, SQL Server can use the previous created -- Non-Clustered Index, but we have indexed a huge amount of NULL values that -- we don't request from this query... SELECT PurchaseOrderNumber, OrderDate, ShipDate, SalesPersonID FROM Sales.SalesOrderHeader WHERE PurchaseOrderNumber LIKE 'PO6%' AND SalesPersonID IS NOT NULL GO
When you run the query, you can see in the Execution Plan that the Non-Clustered Index is referenced by the Query Optimizer.
When we check the size of the index through the Dynamic Management Function (DMF) sys.dm_db_index_physical_stats, you can see that the Non-Clustered Index consists of 132 pages: 1 index root page, and 131 pages in the leaf level.
-- The previous created Non-Clustered Index has 132 pages (1 + 131) SELECT * FROM sys.dm_db_index_physical_stats ( DB_ID('AdventureWorks2014'), OBJECT_ID('Sales.SalesOrderHeader'), NULL, NULL, 'DETAILED' ) WHERE index_id = (SELECT index_id FROM sys.indexes WHERE name = 'idxTest') GO
To make now the index structure more efficient, you can also create a Filtered Non-Clustered Index. When you think back to our query, we only request column values with a Non-NULL value in the column PurchaseOrderNumber. Therefore we can just filter the NULL values out from the index structure. So let’s recreate now the Non-Clustered Index as a Filtered Non-Clustered Index.
-- Recreate the Non-Clustered Index as a Filtered Index and filter out the NULL values CREATE NONCLUSTERED INDEX idxTest ON Sales.SalesOrderHeader(PurchaseOrderNumber, SalesPersonID) INCLUDE (OrderDate, ShipDate) WHERE PurchaseOrderNumber IS NOT NULL AND SalesPersonID IS NOT NULL WITH (DROP_EXISTING = ON) GO
When we check now again the index size through the DMF sys.dm_db_index_physical_stats, you will see now that the Filtered Non-Clustered Index consists of only 29 pages: 1 index root page, and only 28 pages in the leaf level.
In our case the query performance does’t get better, but you have created a much smaller index, and therefore you introduce less overhead in your database. Your Index Reorganize and Index Rebuild operations will be much faster, because they are dealing with a smaller index structure.
Disadvantages of Filtered Indexes
By now you know the advantages of Filtered Indexes in SQL Server. Let’s talk now about the disadvantages. Yes, there are also disadvantages! There are always some pro’s and con’s about everything. Like in real life!
Let’s work now with parameterized T-SQL statements. In my case I create a simple Stored Procedure that accepts a @PurchaseOrderNumber parameter.
CREATE PROCEDURE RetrieveData ( @PurchaseOrderNumber NVARCHAR(25) ) AS BEGIN SELECT PurchaseOrderNumber, OrderDate, ShipDate, SalesPersonID FROM Sales.SalesOrderHeader WHERE PurchaseOrderNumber = @PurchaseOrderNumber END GO
But when you execute now the Stored Procedure and you check the Execution Plan, you will see that the Query Optimizer hasn’t chosen the Filtered Index. You are scanning the whole Clustered Index of the table Sales.SalesOrderHeader.
Why is the Query Optimizer not referencing the Filtered Non-Clustered Index? Because it’s not safe! Imagine that initially the Filtered Index is referenced in the Execution Plan. That Execution Plan get’s cached in the Plan Cache. When you now execute the Stored Procedure again with a different parameter value, SQL Server can reuse the already cached Execution Plan from the Plan Cache.
But what happens if you specifiy a NULL value as input parameter value? Then the cached plan wouldn’t return you a result, because the Filtered Non-Clustered Index doesn’t know anything about the NULL values. So the query result would be nothing, which is wrong! And therefore the Query Optimizer can’t reference a Filtered Index in a parameterized T-SQL statement!
Filtered Non-Clustered Indexes are great – as long as you know their limitations. Normally parameterized T-SQL statements are great, because you don’t pollute the Plan Cache with Adhoc queries. But in combination with Filtered Non-Clustered Indexes in SQL Server they are somehow counterproductive. So please keep that in mind when you work on your indexing strategy.
Thanks for your time,