Covering Index – A “Adam and Eve” Style

We know that in SQL Server there is a limitation on index key which is 900 bytes. This means that the total length of an index must not exceed 900 bytes. Until SQL 2000, we used to create a covering index by concatenating a number of columns to support a query execution. Starting from SQL Server 2005, Microsoft has added a new feature which is known as the “INCLUDE” column index.

In this article we will be researching on covering indexes, its performance comparison, which is between “multiple-columns index” (also known as composite, or concatenated) and “single column index” with the “include” option.

Covering index:

In a simple way, a covering index is one which can satisfy all requested columns in a query without performing a further lookup into the base table (clustered or heap).  So, we can say a covering index is an index which will satisfy JOIN and WHERE clause as well as all the columns from the SELECT statement.

Example of covering index:

Say we have the following SELECT query from a table “tblLarge”,

SELECT  xID ,              -- Primary and Clustered key

        sName1 ,           -- Key column for search

        sName2 ,           -- non-key column

        sName3 ,           -- non-key column

        sIdentifier        -- key column for search

FROM tblLarge

Based on the above select criteria, we may have the following covering indexes:

(a)    Multiple-column index without include option:

sIdentifier +  sName1 + sName2 + sName3

(b)   Multiple-column index with include option:

sIdentifier +  sName1  INCLUDE ( sName2 , sName3)

Please keep in mind that the clustered key does not need be part of the index.

Index Advantages:

The biggest advantage of a covering index is that it completely offloads the locking overheads from a table to the index. This reduces

1.        I/O operation

2.       Row lookup

3.       CPU usage

The overall benefit is a dramatic improvement of query response time.

Some Terms:

Let’s learn what we mean by Key and non-key column. Usually, a column said to be a key column is used on a JOIN or on a WHERE clause. The columns which are not used in search or in join clause are known as non-key columns.

A fact:

It is recommended that an index key should be narrow as possible- this means that Query Optimizer of SQL Server will always try to utilize the smallest index. SQL Server Query optimize is very picky because it is a cost based optimizer.

While creating an execution plan, Query Optimizer evaluates mainly two things; Selectivity and data retrieval cost. Based on the estimated cost of an index, it may not be selective enough so an index may not be used or if it selects a sub-optimal index, then query performance may hinder rather than improvement. In that case Index Hints can be used to force SQL Server to use a particular index.

Our Testing Approach:

1.       We will use a database called “TestDB”.

2.       A table “tblLarge” with 2,000,000 records.

3.       A clustered index on the Primary key “xID”.

4.       We will create two indexes.

5.       Buffer cache will be cleared before query execution.

6.       We will also collect STATISTICS IO output.

Sample Database, Table and Rows creation:

1.       Create a database “TestDB”

CREATE DATABASE TestDB

GO

USE TestDB

GO

2.        Create a “tblLarge” with default fill factor (which is 0 or 100)

SET NOCOUNT ON

IF OBJECT_ID('tblLarge') IS NOT NULL

    DROP TABLE tblLarge

GO

CREATE TABLE [tblLarge](

       [xID] [int] IDENTITY(1,1) NOT NULL,

       [sName1] [varchar](100) NULL,

       [sName2] [varchar](200) NULL,

       [sName3] [varchar](300) NULL,

       [sIdentifier] [char](10) NULL,

       [dDOB] [datetime] NULL,

       [nWage] [numeric](20, 2) NULL,

       [sLicense] [varchar](25) NULL,

 CONSTRAINT [PK_tblLarge] PRIMARY KEY CLUSTERED ([xID] ASC) )

3.       Populate 2,000,000 rows.

SET NOCOUNT ON

INSERT  INTO tblLarge

        ( sName1 ,

          sName2 ,

          sName3 ,

          sIdentifier ,

          dDOB ,

          nWage ,

          sLicense

        )

VALUES  ( LEFT(CAST(NEWID() AS VARCHAR(36)), RAND()*50) ,     -- sName1

          LEFT(CAST(NEWID() AS VARCHAR(36)), RAND()*60) ,     -- sName2

          LEFT(CAST(NEWID() AS VARCHAR(36)), RAND()*70) ,     -- sName2

          LEFT(CAST(NEWID() AS VARCHAR(36)), 2) ,             -- sIdentifier      

          DATEADD(dd, -RAND() * 20000, GETDATE()) ,           -- dDOB

          ( RAND() * 1000 ) ,                                 -- nWage

          SUBSTRING(CAST(NEWID() AS VARCHAR(36)), 6, 7)       -- sLicense

         )

GO 2000000

Sample SELECT query:

We will be using the following SELECT query to perform our test.

DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON

SELECT  xID ,              -- Primary and Clustered key

        sName1 ,           -- Key column for search

        sName2 ,           -- non-key column

        sIdentifier        -- key column for search

FROM    tblLarge

WHERE   sIdentifier = 'AB'

        AND sName1 LIKE 'ABC%'

Any search string can be used to run the above query.

Covering Index with “INCLUDE” option:

In our first test, let’s create two covering indexes with the “INCLUDE” option as follows.

Ind#1

CREATE INDEX [IX_sIdentifier] ON [tblLarge] ([sIdentifier])

INCLUDE ([sName1], [sName2])

Once the index#1 is created, we run the above query, and it provides the following Execution Plan.

Let’s create a variation of the above index and move the “sName1” column to the index key as follows.

Ind#2

CREATE INDEX [IX_sIdentifier_sName1] ON [tblLarge] ([sIdentifier], [sName1])

INCLUDE ([sName2])

When we run the query, we get the following Execution Plan.

Covering Index without “INCLUDE” option:

In our second test, we will create a multiple-column index without the “INCLUDE” option as follows.

Ind#3

CREATE INDEX [IX_sIdent_sName1_sName2] ON [tblLarge] ([sIdentifier], [sName1], [sName2])

Now if we run our query, it will result in the following Execution Plan.

STATISTICS IO Comparison:

When  #X Index	Scan Count	Read-Ahead Reads	Physical Reads	Logical Reads
Ind#1	1	72	2	75
Ind#2	1	0	0	3
Ind#3	1	0	0	4

Index Properties of each Index:

 

 

Review of Query execution:

1.       When we first created the index “ind#1”, query utilized this index although the index is not completely covered. When the query was first executed, there were no statistics for the column “sName1”, so SQL Server created missing statistics.

2.       In case of “ind#2”, all the predicates are found in this index and as well “sName2” which is included with the “INCLUDE” option. So this index has totally covered the query.

3.       For the “ind#3”, we created this index at the end without deleting the previous two indexes. Though the second index is far better than the third index, SQL Server started using this multiple-column index although it is a little expensive.

As an explanation, when we create multiple-column indexes, a multi-column statistics is also created along with the index which provides better cardinality estimations to evaluate the query. However, if we examine the properties of all the indexes, then you will notice that the depth of “ind#3” is 4, which makes it more complex in nature by adding one more leaf level in the b-tree structure.

Summary:

There is a saying which goes like “Query writing is an Art, index is a Science”. As SQL Server uses indexes, it does not mean that it is efficient too. Reviewing the "Index Depth" is one of the critical factors for a performance boost while creating a covering index for a query.

While creating indexes, we should not forget that there is a cost and penalty to maintain indexes where DML operation happens very frequently, and which often reduces the benefit of having indexes. Also we should not have duplicate and overlapping indexes, unless there is a very specific reason.

Read More:

SQL Server Optimization

http://msdn.microsoft.com/en-us/library/aa964133(v=sql.90).aspx

Increase fan-out to reduce index depth

http://www.sqlmag.com/blog/sql-server-questions-answered-28/performancetuning/increase-fanout-reduce-index-depth-140583

Clustered and Nonclustered Indexes Described

http://msdn.microsoft.com/en-us/library/ms190457.aspx

Introduction to Indexes

http://www.sqlservercentral.com/articles/Indexing/68439/