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

Posted on December 21, 2010, in 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: