Using Filtered Indexes to Improve Performance

A Filtered Index is a new index type introduced in SQL
Server 2008. In simple terms, rather than adding a non-clustered index to the
entire table, you can add an index to a filtered data set.

The syntax for creating a filtered index is :

ON <Table Name>(<Column(s)>)
WHERE <Filter Condition>

So, the only addition to the standard non-clustered index is the
filter condition. 

Let us start with an example.

In the AdventureWorks2012 database, there is a table
called Production.WorkOrder . If you closely examine the data, you will
notice that there are 72,591 rows and only 729 of them have values.  There
is an index on this column called IX_WorkOrder_ScrapReasonID .

Let add another column (newScrapReasonID) populate
from the ScrapReasonID.

ALTER TABLE [Production].[WorkOrder]

ADD [newScrapReasonID] INT;


UPDATE [Production].[WorkOrder]

SET [newScrapReasonID] = [ScrapReasonID]

Let us create an index for the newScrapReasonID
column but this time we will create it as a filtered index.

CREATE NONCLUSTERED INDEX [NCI_WorkOrder_ScrapReasonID_Filtered] ON [Production].[WorkOrder]

       [newScrapReasonID] ASC

WHERE ([newScrapReasonID] IS NOT NULL)

In the above filtered index, the index will be created only for
the columns where ScrapReasonID is not null.

Before analyzing any performance, let us verify index sizes
by using following query :

SELECT IndexName,

 i.index_id ,

 8 * SUM(a.used_pages) AS 'Index Size(KB)'

 FROM sys.indexes AS i

 INNER JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id

 INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id


 GROUP BY i.index_id,

 ORDER BY i.index_id

Output for the above query is :

Note that the index size has been dramatically reduced from 880 KB to 32 KB.

Now let’s run two queries :

SELECT [ScrapReasonID]

FROM [Production].[WorkOrder]


SELECT [newScrapReasonID]

FROM [Production].[WorkOrder]

WHERE [newScrapReasonID] IS NOT NULL

Here is the execution plan for both queries.  So,
query which uses filtered index has less cost.

Using Filtered Index

Having seen the above behavior, you might be tempted to think
that a filtered index can be used in most cases. Let us see the following example.

First create a fresh filtered index.

CREATE INDEX NCI_SalesOrderDetail_ProductID_FLT

ON Sales.SalesOrderDetail (ProductID)

WHERE ProductID = 900 

Now, run following two queries.

FROM Sales.SalesOrderDetail 
WHERE ProductID = 900


DECLARE @ProductID INT = 900
FROM Sales.SalesOrderDetail 
WHERE ProductID = @ProductID

The two queries are very similar, the only minor different is the second query uses a
parameter.  Though the result is same, let us examine the query plan :

Note the first query uses the
filtered index whereas second query uses standard index :

Thus it is crucial to note that if your query has parameters it will not use filtered
indexes. Therefore, when selecting a filtered index, it should be a column
where it is not used for parameter.  Most appropriate columns are, status columns,
gender columns etc.

Creating Filtered Unique keys

Let us say we have a table like following.


  PersonID INT,

In this table, PearsonID is nullable but if you have
a value it should be unique.  So, let us create a unique index.

ON Data(PersonID)

This statement will fail since there are multiple null
values for PearsonID. :

Msg 1505, Level 16, State 1, Line 2

The CREATE UNIQUE INDEX statement terminated because a duplicate key
was found for the object name ‘dbo.Data’ and the index name
‘IDX_Data_PersonID’. The duplicate key value is (<NULL>).

statement has been terminated.

So the option is creating a filtered index :

ON Data(PersonID)

Filtered Index Compared with Index View

You might think that index view also can be used for the
same purpose. There are pros and cons to using both options. A major advantage in using an index view is
that you have the option of creating the index for a view where you have
multiple tables, whereas a filtered index can be created only on one table.

The filtered index does have a few definite advantages over index views.

  • Index view has to be a unique clustered key whereas a filtered
    index need not to be unique
  • You can build a filtered index online which cannot be done for an indexed view.

New Sp_helpindex

For retrieving index information, we tend to
use sp_helpindex. However, sp_helpindex does not provide other index attributes
such as include columns and filtered conditions.  There are many scripts available
if you google. Out of all, this script
suited me most.  This script will create procedure called sp_helpindexall.

SELECT ScrapReasonID
FROM WorkOrder WITH (INDEX (NCI_WorkrOrder_ScrapReasonID_Filtered))
WHERE ScrapReasonID = 3

So this will provide you all the indexes with included
columns and filter definitions.

Using FilteredIndex with Index Hint

What if you are using a filtered index and your filter
condition (or Where clause) contradicts the filter index like following query.

SELECT ScrapReasonID
FROM WorkOrder WITH (INDEX (NCI_WorkrOrder_ScrapReasonID_Filtered))
WHERE ScrapReasonID = 3

You might tend to believe that query will ignore the index
clause and will continue to execute, however this is not the case and the output will be :

Msg 8622, Level 16, State 1, Line 2
Query processor could not produce a query plan because of the hints
defined in this query. 
Resubmit the query without specifying any hints and
without using SET FORCEPLAN

Finally note that, NOT IN , LIKE, SUBSTRING, LEFT are will not work work for the filter condition in Filtered index.


No comments yet... Be the first to leave a reply!