Ray Maor

Ray Maor

R&D manager for our software products. With 20 years of DBA expertise and writes in 15 programming languages. Responsible for the architecture, workflow design and implementation of key algorithms for Experad's software products.

Using Covering Indexes In a Query

SQL server allows us to create multi-columned indexes. In most of today’s systems –it’s required.

The automatic index recommendations that are provided in the execution plan work well. However, when our query is more sophisticated, the optimizer will not always recommend the proper index and it will be required to manually find the proper index.

A complex query can have multiple nested level, embedded views, large amount of objects and more. Each query will use a single index access for a give object, at times the optimizer will combine indexes if one of them is the clustered index.

Adding another index, especially a larger (wide) index, can have an impact on an update, insert and delete statements to a degree. Most systems have more select statements than UID statements and therefor, DBA’s tend to add multiple indexes in different variations on their tables, especially the fact tables.

A covering index is an index that holds all referring columns from the query for the given table.

When accessing a table through an index, these are the steps:

  1. Index Access to filter out the records (filter from where / join parts of the query) -> Row IDs into a memory table
  2. Using the Row IDs from #1 we go to the table and retrieve the records

This is the normal and most common table access.

When we are using a covering index, we don’t have a table access at all (#2).

For example, let’s take this simple query from the sample Northwind database.

The query retrieves a list of cities for a selected country and the total income per city on the third yearly cycle.

select o.Ship City ,  sum (od.Quantity*od.Unit Price) Total

from [Orders] o inner join [Order Details] od on o.Order ID = od.Order ID

where o.Cycle = 3 and o.Ship Country =’USA’

group by o.Ship City

order by 2 desc

A quick look at the query will show us there are 2 tables (Orders, Order Details) that can be indexed. Without any indexes, the execution plan will be a 2 table scans and the IO statistics will be:

Table ‘Order Details’. logical reads 9339

Table ‘Orders’. logical reads 20297

The first index we will want to add is for the where predicates on table orders:

Orders_IX1 – Orders (ShipCountry, Cycle)

And in this case, the optimizer will use the index and with it will make a RID lookup at the table itself. In case the table will have a clustered index – the lookup will be on the clustered index. We also went down from 20K reads to 125 reads.

Table ‘Order Details’. logical reads 9339

Table ‘Orders’. logical reads 125

If we want to create a covering index, we will have to include the columns that are in the group by and in the select list. In this scenario, as we are grouping by Ship City, it is better to keep it in the Keys of the index. If we just needed to have it in the select list, we could have added it in the Including section of the index. We will create this new index and will preserve the previous index to see which one the optimizer will select. Notice that we have chosen the most selective column first, then the second where the predicated column, then the joined column and we left the group by column to last as it will be the last operation in the execution.
create index Orders_IX2 on Orders (Ship Country, Cycle, Order ID, Ship City)

After running the query, we can see that in the new execution plan there is no table access at all. The logical reads went down to 4 (after originally being at 20K).

Table ‘Order Details’.logical reads 9339
Table ‘Orders’. logical reads 4

We can use another Covering index for table [Order Details] to complete the improvement. In this case, we don’t have any filtering columns, so the first column will be the Order ID that is from the join predicate, that is also considered to be a filtering column.

create index Orders_Details_IX1 on [Order Details] (Order ID) Include (Quantity, Unit Price)

The results are another significant reduction in the logical reads and no table access.

Table ‘Order Details’. logical reads 416

Table ‘Orders’. logical reads 4

Share this post

Share on facebook
Share on linkedin
Share on twitter
Share on whatsapp