Covering Indexes

Microsoft continues to improve indexes and options for additional performance enhancements. One I see frequently is the need for a covering index. Before we look at those options, let’s talk about the need for a covering index.

The term covering index was created probably a decade ago. The idea is for the index to cover all columns need to improve the performance of a query. This includes the filters in the WHERE clause as well as the columns in the SELECT section of a query. Before Include Columns and Column Store indexes, this had to be accomplished by adding the columns to index tree structure.

Here is an example query that would benefit from a covering index using the Adventure Works database from CodePlex:

USE [AdventureWorks2014] GO SELECT soh.[SalesOrderID], [RevisionNumber], [OrderDate], [DueDate]     , p.Name, p.ListPrice, sod.OrderQty   FROM [Sales].[SalesOrderHeader] soh     INNER JOIN [Sales].[SalesOrderDetail] sod ON sod.SalesOrderID = soh.SalesOrderID       INNER JOIN [Production].[Product] p ON p.ProductID = sod.ProductID   WHERE OrderDate Between '20130101' AND '20130101'    AND p.Color = 'Black'

In the case of the above query, the Product table has an Index Seek on the clustered index which implements a Seek Predicate on the ProductID, but the Color column has a separate Seek which is the Predicate part of the query plan below. So, work is done in the Data Pages of the Clustered Index to find the proper Color value to match the second part of the WHERE (AND …) clause.

We can create a new index to “cover” the ProductID and Color in order to have only a Seek Predicate in the execution plan.

CREATE NONCLUSTERED INDEX idxProduct_ProductIDColor ON [Production].[Product]   (ProductID, Color) GO

Now, the problem is the execution plane shows a Key Lookup to get the columns Name and List Price.

To cover the columns Name and ListPrice in the Product table, we need to add those columns to our index. Instead of adding to the end of the Column list like the following…

 

CREATE NONCLUSTERED INDEX idxProduct_ProductIDColor ON [Production].[Product]   (ProductID, Color, Name, ListPrice) GO

…we can include it in the data page part of the index by using the INCLUDE part of the CREATE INDEX syntax.

CREATE NONCLUSTERED INDEX idxProduct_ProductIDColorIncludeListPrice     ON [Production].[Product]         (ProductID, Color)     INCLUDE (Name, ListPrice) GO

Now, we have improved the performance of the query and limited the search part of the index structure to only the columns need for the Seek Predicate. In the image below, you can see in the output list the columns Name and ListPrice in addition to the Seek Predicates with extra Seek. The Object section of the display shows the index being used - idxProduct_ProductIDColorIncludeNameListPrice.

In conclusion, Microsoft has really helped us over the years with improvements to the Index creation. After we have created this new index, it is a good idea to start monitoring the indexes with DMVs/DMFs to see how often they are used (Reads and Writes), and if other indexes can be removed because they are not being utilized.