Improving SQL Performance using Covering Indexes


What is a Covering Index?

An index that contains all required information to resolve the query is known as a “Covering Index” – it completely covers the query.

Covering Index includes all the columns, the query refers to in the SELECT, JOIN, and WHERE clauses.

Key Columns and Non-Key Columns –

Key columns are columns that an index (clustered or non-clustered) is created on. An index with non-key columns can significantly improve query performance.

By including non-key columns, we can create non-clustered indexes that covers more queries.

The non-key columns have the following benefits:

  • They can be data types that are not allowed as index key columns.
  • They are not considered by the Database Engine when calculating the number of index key columns or index key size.

Avoiding Key Lookups —

When we use a non-clustered index to fetch the results of a particular query, the SQL Server query optimizer uses a ‘Key Lookup’ to retrieve any non-key data. In other words, once SQL Server optimizer has used the non-clustered index to identify each row that matches the query conditions, it then retrieves the column information for those rows from the data pages of the table.

Key Lookups can be detrimental to performance for queries that return large result sets.

By including non-key columns in non-clustered indexes, we can dramatically improve query performance. The covering index helps us to avoid the round trip to the table to satisfy the request, since all of the columns requested exist in the non-clustered index itself. This greatly reduces logical and physical reads, hence boosting performance.

Let me explain the concept of Covering Index with an example —

Suppose we execute the SQL Query below

SQLQuery

Generated Query Execution Plan

ExecutionPlan1

StatisticsBefore

Problem Statement

The problem with the initial query was that it used to took 15ms time to execute.I just have few records in my test table, but if we execute this query against a bigger table – it would take more time for sure. This was also one of the frequently running query in my application & hence I wanted to fine tune this query.

Generally when I start doing a performance analysis, apart from looking at the Execution Plan – I also look at statistics, which will show me the amount of disk activity and time taken by the SQL Server Optimizer to execute a query.

We can turn on statistical information in SQL Server, by executing the below statements –

SET STATISTICS IO ON

SET STATISTICS TIME ON

Looking at the stats, it shows that there are 367 logical reads – which means that 367 pages were read from the data cache. The query execution time was 15ms. If we can convert the scan operation on ‘Department’ table to a seek operation, we can bring down the logical read count & operation will be faster.

Query Cost is HIGH because we have an INDEX SCAN Operation.This operation is expensive and needs to be modified for improving performance.

Resolution

Create a new Non Clustered Index on ‘Name’ column of ‘Department’ table.

Index1

Query Execution Plan after non clustered Index creation – –

ExecutionPlan2

Problem Statement

There is a Key Lookup Operation which has a cost of 33% on the Department table and can have negative impact on query performance.

Resolution

Create a new Non Clustered Covering Index and include the ‘GroupName’ column of ‘Department’ table.

An index with non-key columns can significantly improve query performance.

iNDEX2

Query Execution Plan after covering index creation

eXECUTIONpLAN3

StatisticsAfter

We were successfully able to convert the Index Scan operation to Index Seek, and also got rid of the Key LookUp operation.

The statistics shows that there were just 3 logical reads(compared to 367 earlier) and the query execution time also came down to just 1ms(compared to 15ms earlier)

Again I just have few records in my test table, if we execute a similar query against a bigger table – the time & CPU benefits will be very much visible.

3 thoughts on “Improving SQL Performance using Covering Indexes

  1. Please excuse my ignorance, but your final execution plan looks as bad as when you started. Can you explain how you last execution plan is better than what it was in the beginning? Thanks

    Liked by 1 person

    1. That is a great question Doug.

      The problem with the initial query was that it used to take 15ms time to execute. After creating the covering index, the query execution time came down to just 1ms.
      I just have few records in my test table, but if we execute this query against a bigger table – the benefits will be very much visible.
      If this is a frequently running query in our project, I hope you will agree that it will be a performance boost for sure.

      Generally when I start doing a performance analysis, apart from looking at the Execution Plan – I also look at statistics, which will show me the amount of disk activity and time taken by the SQL Server Optimizer to execute a query.

      We can turn on statistical information, by executing the below statements –
      SET STATISTICS IO ON
      SET STATISTICS TIME ON

      Statistics – Before —

      Table ‘Department’. Scan count 1, logical reads 367, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
      SQL Server Execution Times:
      CPU time = 0 ms, elapsed time = 15 ms.

      Statistics – After creating the covering index —

      Table ‘Department’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
      SQL Server Execution Times:
      CPU time = 0 ms, elapsed time = 1 ms.

      In short, by creating the covering index I was able to bring down the query execution time, decrease the number of logical reads and improve the execution plan for this query by getting rid of the SCAN operation.
      Again I just have few records in my test database, if we execute this query against a bigger table – the benefits will be very much visible.

      I appreciate your comment Doug. I have modified my blog post now to reflect this information.

      Like

  2. Samir,
    Great example of HOW a Covering Index minimizes wasted effort that is unnecessary in most SQL installations observed in my experience.
    Keep up the GREAT documentation effort.
    Thanks for presenting at Steel City Sql Server on 5/17/2016 at Samford University.

    Like

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