Total Pageviews

Friday, March 29, 2013

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

Increase fan-out to reduce index depth

Clustered and Nonclustered Indexes Described

Introduction to Indexes

3 comments:

  1. Excellent Stuff, nicely explained with rquire resources

    ReplyDelete
  2. Tableau consultant
    SQIAR (http://www.sqiar.com/solutions/technology/tableau) is a leading Business Intelligence company.SQIAR Office Located in London.Sqiar Provide Tableau software Consultancy.

    ReplyDelete