Tuesday, January 4, 2011

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%.

No comments:

Post a Comment

All suggestions are welcome