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.

Idera Ace

I started using a product called SQL Diagnostic Manager from Idera about 10 years ago at a Home Health company. It was very useful with monitoring a SQL Server instance and helped us estimate the future size of databases based on historical data. The numbers were very accurate for a 6 month and one year forecast.

I also discovered other tools for indexes and current state from the same company: Idera. Now, I have been selected as an Idera ACE for the Idera Community program to help speakers in the SQL Server Community.

They have given us the use of their peer network in order to blog about SQL Server and other Data topics. The best part is representing SQL Server and Idera at community events like SQLSaturday and PASS Summit. We will even be able to talk on webcasts with their support people.

Please visit their site and find out all about the wonderful tools for monitoring your systems. I got a great demo of BI Manager with capabilities to look at SSIS, SSAS and SSRS. The director of BI Manager development was a BI developer and he knows what needs to be captured through PerfMon(s) and DMV(s).

I am really looking forward to testing this monitoring tool during my upcoming sessions. Wish I had it before the Summit during my three hour tabular presentation.

The next 3 events I have scheduled are Live!360 (Orlando), SQLSaturday Austin and SQLSaturday Pensacola.

For Live! 360, here is a discount promo: Register for #Live360 Orlando with code LSPK49 and save $600! bit.ly/LSPK49_Reg