Filtered Indexes

                There are many performance and space-saving strategies that we can employ as Database Administrators and Developers.  One of the tried-and-true methods is through the utilization of filtered indexes.  Below is how you would create a filtered index:

create index IX_YourIndexName

on YourTable(YourKeyColumn)

where YourColumn < 10

go

                In the above snippet, you can see that creating a filtered index is very much like creating any other index:  The alteration is through the use of the WHERE clause to narrow down the scope of the indexed rows.  Namely two advantages of filtered indexes are the saved space, as well as the maintenance of the index could potentially be less.

                When would you use a filtered index?  Often times we have tables that contain data that won’t ever be queried (or queried often enough for the storage consumption and indexing benefits).  For instance, if you only typically look for products that actually have a cost associated, you are narrowing down your query.  And because it may heed us to have an additional index on this table, the rows that have no cost associated with them will most likely never be retrieved (or not enough to warrant inclusion in the index).

                Here’s an example of real-time space-saving proof.  (The below example uses the AdventureWorks2012 sample database which can be found here)

                I wanted to choose a relatively large table in the AdventureWors2012 database to emphasize the filtered index advantage, so I chose the Sales.SalesOrderDetail table.  Run the below query to view a summary of the indexes on this table:

use AdventureWorks2012

go

select

       object_name(p.object_id) as table_name,

       i.name as index_name,

       i.type_desc as index_type,

       ps.row_count,

       ps.reserved_page_count,

       ps.reserved_page_count * 8 as reserved_space_kb

from sys.partitions p

inner join sys.indexes i

on p.object_id = i.object_id

and p.index_id = i.index_id

inner join sys.objects o

on p.object_id = o.object_id

inner join sys.dm_db_partition_stats ps

on p.partition_id = ps.partition_id

where o.is_ms_shipped = 0

and object_name(p.object_id) = 'salesorderdetail'

order by ps.row_count desc

                Now focus in on the nonclustered index called IX_SalesOrderDetail_ProductID:  It consumes a total of 2624 KB of space.  In order to get what the indexed columns are for this index, we can run the following simple query:

use AdventureWorks2012

go

select

       object_name(i.object_id) as table_name,

       i.name as index_name,

       i.type_desc,

       c.name as column_name,

       ic.is_included_column

from sys.indexes i

inner join sys.index_columns ic

on i.object_id = ic.object_id

and i.index_id = ic.index_id

inner join sys.objects o

on i.object_id = o.object_id

inner join sys.columns c

on c.object_id = i.object_id

and ic.column_id = c.column_id

where o.is_ms_shipped = 0

and object_name(i.object_id) = 'salesorderdetail'

order by table_name, i.type_desc, index_name

So now we know that the indexed column is ProductID (as the index name would have suggested).  Let’s go ahead and drop that index (for testing purposes only).

-- drop the out-of-the-box NCI on ProductID

drop index IX_SalesOrderDetail_ProductID

on Sales.SalesOrderDetail

go

                Why?  Well say that for whatever business domain reason we typically never query records in this table that have a ProductID of greater than 800.  We don’t want the overhead of maintaining this extra index, so we are going to drop it with hopes of a newer, smaller filtered index.  Now create the filtered index based off of our guidelines:

-- create the filtered index

create index IX_SalesOrderDetail_ProductID_Filtered

on Sales.SalesOrderDetail(ProductID)

where ProductID < 800

go

                There you have it.  Now we have a filtered index on the Sales.SalesOrderDetail.  Go ahead and run the index consumption query again to find out our space savings:

use AdventureWorks2012

go

select

       object_name(p.object_id) as table_name,

       i.name as index_name,

       i.type_desc as index_type,

       ps.row_count,

       ps.reserved_page_count,

       ps.reserved_page_count * 8 as reserved_space_kb

from sys.partitions p

inner join sys.indexes i

on p.object_id = i.object_id

and p.index_id = i.index_id

inner join sys.objects o

on p.object_id = o.object_id

inner join sys.dm_db_partition_stats ps

on p.partition_id = ps.partition_id

where o.is_ms_shipped = 0

and object_name(p.object_id) = 'salesorderdetail'

order by ps.row_count desc

                We should get a reserved_space_kb of now roughly 968 KB.  Due to the use of a filtered index (intelligently created, of course), we were able to knock down the size of that index to about 37% of the original size.

                Filtered indexes can be a great way to minimize wasted storage and index maintenance overhead.  With the knowledge of this tool and the appropriate application, it can yield impressive results.

                The applicable code for this blog post can be found on my GitHub SQLSalt repository.  The following links to the source code are:

Indexes_Physical_GetRowsAndSpacePerIndex.sql

Indexes_Structure_GetIndexedRowsAndIndexesForAllTables.sql

                If there are any comments, questions, or issues please feel free to leave a comment or email me at sqlsalt@gmail.com.