Sunday, May 3, 2009

Performance improvements with filtered indexes in SQL 2008

In SQL 2008 filtered indexes are introduced. A filtered index allows us to create a filter to index a subset of rows within a table. A filtered index will:
  1. Improve query performance. Statistics are more accurate which can result in better query plans. 
  2. Reduce index maintenance costs. An index is only maintained when the data in the index is changed.
  3. Reduce index storage costs.
Filtered indexes can be very useful if you have one of the following situations:
  1. Columns which contains mostly NULL values and where queries retrieve only the rows where the data is NOT NULL. 
  2. When you want to enforce uniqueness for a set of values. With a filtered index you can create a unique index for the set of values. For instance
  3. When analyzing data on a specific data range. For instance you have a table with records for the last 4 years (2006,2007,2008,2009). You are analyzing only the year 2009. A filtered index will look like
    CREATE INDEX YEAR2009 ON Transactions (Year,Period, Amount) WHERE Year = 2009
To see the difference in index sizes on a table create a normal index and a filtered index. For instance:
  1. CREATE INDEX YEAR ON Transactions (Year,Period, Amount)
  2. CREATE INDEX YEAR2009 ON Transactions (Year,Period, Amount) WHERE Year = 2009
Use next script to see the difference in index size:


SET @TableName = 'fill in the tablename'

SELECT db.index_id,, IndexSizeKB = SUM(page_count * 8)
FROM sys.dm_db_index_physical_stats(db_id(),object_id(@TableName),NULL,NULL,'DETAILED') db
INNER JOIN sys.indexes SI ON SI.index_id = db.index_id AND SI.object_id = object_id(@TableName)
GROUP BY db.index_id ,
ORDER BY db.index_id desc,

No comments: