Chris Umbel

SQL 2008 filtered indexes

there are so many cool new features available in sql server 2008 that many a DBA are excited. as an index-tuning man i've been intrigued by filtered indexes which essentially allow you to slap a WHERE clause on an index making it small and focused on a strategic subset of the tables data. you know, the sort of thing that would have required an indexed view in the past.

in order to test them out i created a simple table in a scratch database and filled it with data using DataDude.

CREATE TABLE [dbo].[Sales](
	[Sale_ID] [int] IDENTITY NOT NULL,
	[Complete] [bit] NOT NULL,
	[Location] [int] NOT NULL,
 CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED 
(
	[Sale_ID] ASC
) 

the table was filled with 10000 rows with [Complete] = 1 and 100 rows with [Complete] = 0. Location was a random value between 1 and 5.

if i issue the following query:

SELECT [Location], COUNT(*)
FROM [Sales]
WHERE [Complete] = 0
GROUP BY [Location]
to break out counts by location for incomplete sales the following execution plan is chosen because i have no index on the location column:

i could easily increase the efficiency of the query by creating the following filtered index:

CREATE INDEX IX_Location_ForComplete 
ON [Sales] ([Location]) 
WHERE [Complete] = 0
resulting in:

in comparison, 25 reads resulted without the filtered index and 3 reads resulted with it. quite a significant improvement.

by now i bet you're asking, "couldn't we just create a composite index on [Sales] ([Complete], [Location]) and then queries for complete orders could benefit as well?" well, yes. you could, and it will result in a much larger index that results in higher read counts (5 in this case). also it could be very nasty when page splits occur as the database grows.

the truth is that there are situations where you may only issue certain queries against predictable portions of a table, such as incomplete orders (which are the vast minority of rows in the table in this case). why waste the space and IO on values you know will not be relevant? why waste the time splitting pages while indexing the 90+% of your table you're not interested in or doesn't change anymore?

filtered indexes are yet another interesting feature provided by SQL 2008 that have me eagerly awaiting RTM.

Wed Jun 11 2008 00:00:00 GMT+0000 (UTC)

Follow Chris
RSS Feed
Twitter
Facebook
CodePlex
github
LinkedIn
Google