Monday, January 30, 2012

Filtered indexes


A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Filtered indexes can provide the following advantages over full-table indexes:

Improved query performance and plan quality

A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

Reduced index maintenance costs

An index is maintained only when data manipulation language (DML) statements affect the data in the index. A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is affected. It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics.

Reduced index storage costs

Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.


Here is a small example of using Filtered Indexes from BOL.

use AdventureWorks2008
go

select *
  from Sales.SalesOrderHeader
Go

-- create a duplicate table
select 
  into Sales.SOHdup 
  from Sales.SalesOrderHeader
Go

-- check the table
select *
  from Sales.sohdup


--check if there are any indexes
sp_helpindex 'Sales.SOHdup'
Go

-- create a non clustered index

create nonclustered index idx_NC_SOID on Sales.SOHdup(SalesOrderID)


sp_helpindex 'Sales.SOHdup'
GO

-- using DMV
SELECT *
  FROM sys.dm_db_index_physical_stats ( DB_ID(N'AdventureWorks2008')
                                      , OBJECT_ID (N'Sales.SOHDup')
                                      , NULL
                                      , NULL
                                      , 'detailed');
GO


sp_spaceused 'Sales.SOHdup'
GO
SELECT 
  FROM sys.indexes
 WHERE object_id = object_id('Sales.SOHDup')

-- EP means Execution Plan

--select all records and see the EP
SELECT salesorderID
  FROM Sales.sohdup

-- now we want to create a filtered index for the following query
SELECT salesorderID
  FROM Sales.sohdup 
 WHERE salesorderID >= 58659

--first drop the exiting index
DROP INDEX idx_NC_SOID ON Sales.SOHdup

-- create a filtered index
CREATE NONCLUSTERED INDEX idx_NC_SOID ON Sales.SOHdup(SalesOrderID)
 WHERE salesorderID >= 58659
Go

-- now see the usage on index in the EP
SELECT salesorderID
  FROM Sales.sohdup 
 WHERE salesorderID > 58659


-- now observe the size and stats for the filtered index

SELECT i.[object_id]
     , i.name
     , i.index_id
     , p.partition_number
     , p.rows             as [#Records]
     , a.total_pages * 8  as [Reserved(kb)]
     , a.used_pages * 8   as [Used(kb)]
  FROM sys.indexes as i
 INNER JOIN sys.partitions as p ON i.object_id = p.object_id
                               AND i.index_id = p.index_id
 INNER JOIN sys.allocation_units as a ON p.partition_id = a.container_id
 WHERE i.[object_id] = object_id('Sales.SOHDUP')
-- AND i.index_id = 1 -- clustered index
 ORDER BY p.partition_number
go


Try on large tables on your environment and see how it can improve your performance.

No comments:

Post a Comment