SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

image

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.

KeyLookup

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.

IncludeColumns

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.

The Smiling DBA

Thomas LeBlanc is a Business Intelligence Consultant/Data Warehouse Architect in Baton Rouge, Louisiana. He uses his 25+ years in IT to help develop OLTP systems with normalized databases for high-performing T-SQL and end-to-end dimensional data marts using SSIS, SSAS, PPS, and Excel. His SQL Server certifications include MCSA 12, MCITP 08 BI and DBA, MCITP 2005 DBA, and MCDBA 2000. As a PASS volunteer, he is current chair of the Excel BI virtual chapter, past chair of the Data Architecture virtual chapter, and past virtual chapter mentor. He has helped the Baton Rouge SQL Server User Group with SQLSaturdays and speaks at local IT meetings.

Comments

Leave a comment on the original post [thesmilingdba.blogspot.com, opens in a new window]

Loading comments...