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 ProductIDof 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_kbof 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:
                If there are any comments, questions, or issues please feel free to leave a comment or email me at sqlsalt@gmail.com.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s