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