>Sql Server Performance: Filtered Index a beauty of Sql Server 2008


>

Filtered index is one of the beauties of Sql Server 2008 which is very helpful to increase query performance with less index storage. A non clustered index with WHERE clause, at the time of creation, is called a Filtered index. Filtered index basis on well defined subset of data from very large tables. Let’s see filtered index in action.
SET STATISTICS IO ON
USE [AdventureWorks]
GO
SELECT  SalesOrderID,
        CarrierTrackingNumber,
        OrderQty,
        ProductID,
        UnitPrice
FROM    Sales.SalesOrderDetail
WHERE   CarrierTrackingNumber LIKE ‘%98’
Table ‘SalesOrderDetail’. Scan count 1, logical reads 1238, physical reads 0
 
To avoid clustered index scan, lets create a covering non-clustered index. Covering index is used to avoid bookmarks lockups.
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_CarrierTrackingNumber]
ON [Sales].[SalesOrderDetail] ( [CarrierTrackingNumber] ASC )
    INCLUDE ( SalesOrderID, OrderQty, ProductID, UnitPrice )
WITH (DROP_EXISTING = ON)
ON  [PRIMARY]
GO
SELECT  SalesOrderID,
        CarrierTrackingNumber,
        OrderQty,
        ProductID,
        UnitPrice
FROM    Sales.SalesOrderDetail
WHERE   CarrierTrackingNumber LIKE ‘%98’
Table ‘SalesOrderDetail’. Scan count 1, logical reads 639, physical reads 0
Logical reads dropped from 1238 to 639 but still we can increase query efficiency by converting our covering non-clustered index to a filtered index by including WHERE clause when creating index. In our example we just want to add rows in index where CarrierTrackingNumber is not null. Keyword DROP_EXISTING is used to re-create existing index with single atomic step.
CREATE NONCLUSTERED INDEX [IX_SalesOrderDetail_CarrierTrackingNumber]
ON [Sales].[SalesOrderDetail] ( [CarrierTrackingNumber] ASC )
    INCLUDE ( SalesOrderID, OrderQty, ProductID, UnitPrice )
WHERE [CarrierTrackingNumber] IS NOT NULL — Place a filter
WITH (DROP_EXISTING = ON)
ON  [PRIMARY]
Table ‘SalesOrderDetail’. Scan count 1, logical reads 429, physical reads 0
Results shows an improvement in logical reads up to 33%.
Advertisements

Posted on January 4, 2011, in Index, Performance Tuning. Bookmark the permalink. Leave a comment.

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

%d bloggers like this: