Tuesday, December 21, 2010

Sql Server Performance: Covering Index, A performance booster for query


In most cases, an index is used to quickly locate the data record(s) from which the required data is read. In other words, the index is only used to locate data records in the table and not to return data.
A covering index is a special case where the index itself contains the required data field(s) and can return the data. (wikipedia)
Let’s create a covering index and compare performance, especially when one or more columns are missing in covering index but on query side included in select clause.
USE [AdventureWorks]
GO
-- Drop if already exists
IF EXISTS(SELECT 1 FROM sys.indexes WHERE name = 'ix_FirstName')
DROP INDEX [ix_FirstName] ON [Person].[Contact] WITH ( ONLINE = OFF )

GO
CREATE NONCLUSTERED INDEX [ix_FirstName] ON [Person].[Contact]
(
            [FirstName] ASC
)
--Included columns
INCLUDE ( 
[ContactID],
[NameStyle],
[Title],
[MiddleName],
[LastName],
[Suffix],
[EmailAddress],
[Phone],
[ModifiedDate])  ON [PRIMARY]
GO
This covering index contains values for following 9 columns
1.     [ContactID]
2.     [NameStyle]
3.     [Title]
4.     [MiddleName]
5.     [LastName]
6.     [Suffix]
7.     [EmailAddress]
8.     [Phone]
9.     [ModifiedDate]

Let’s check comparative performance of two queries, in first one all columns are satisfied from our above created index and other one contains one extra column in select clause.

USE AdventureWorks

SELECT    [ContactID],[NameStyle],[Title],[MiddleName],
[LastName],[Suffix],[EmailAddress],[Phone],[ModifiedDate]
FROM    Person.Contact
WHERE   FirstName LIKE N'Phil'

-- Query with one extra column [PasswordHash] which is not included in ix_FirstName index
SELECT    [ContactID],[NameStyle],[Title],[MiddleName],
[LastName],[Suffix],[EmailAddress],[Phone],[ModifiedDate],[PasswordHash]
FROM    Person.Contact
WHERE   FirstName = N'Phil'


Lesson Learned: Create covering indexes for best query performance and all
    columns must be satisfied by used covering index.

No comments:

Post a Comment

All suggestions are welcome