Improving the response times of queries in SQL Server is a common task for DBAs and developers. In this article, we will talk about how you can effectively use covered indexes to improve your performance, and when you should use them.

What is a covering index?

A covering index is an index that has all the information a query needs to run. When a covering index is used, the table is never actually read at all, as everything the query needs is already in the index. this means the performance of a covering index is often dramatic.

Take the following example from Microsoft’s Adventureworks database.

SELECT
      p.LastName, p.FirstName, p.MiddleName, p.PersonType
FROM  Person.Person AS p
WHERE p.LastName = 'Diaz'
ORDER BY
      p.LastName, p.FirstName, p.MiddleName;

This query performs pretty well, because there is already an index with LastName, FirstName, and MiddleName as key columns. Look at what happens if you include PersonType in the index, making it a covering index.

The top query uses the original index. It uses an index seek to find exactly the rows it needs, and they are even already sorted in the right order. Notice that 99% of its performance goes to a key lookup though.  A Key Lookup is when it uses the index to find the row in the table, which is the common way an index works.

In the bottom query, the query uses a new version of the same index, but PersonType is included. The addition of this one extra column means that no key lookups are needed, because the index contains all the information the query needs. That makes it a covering index. In this simple example, that one very small change makes the query 184x faster!

How do I write a good covering index?

The guidelines behind writing a good covering index are similar to any other index.

• In the index key, use only columns involved in filtering or sorting.
• Order the key columns thoughtfully.
• Use INCLUDE to add any additional columns you need to make the index covering

In order for an index to be covering, it needs to have every column used by the query, but it doesn’t have to put them all in the key, and it often shouldn’t. If you don’t use the column in filtering (including joining) or sorting (including grouping), then consider whether it needs to be in the key. Putting the column in the INCLUDE or WHERE instead can help your index be its best.

Key column order

The order of columns inside an index will be important as well. If the order doesn’t match well to the needs of the query, then it will not perform optimally, and may not even be used at all. This article won’t go too deeply into how to order you index’s key columns, but here is a quick list of guidelines.

• The importance of the column to the query’s performance goes left to right.
• Columns used for filtering should be listed with the most unique first.
• Columns used for sorting should match the sort of the query, including the order they appear in and whether they are ascending or descending (bonus points if you know when it doesn’t matter)

Re-usability

Finally, consider the re-usability of the index. Covering indexes can be expensive, so reusing them is even more beneficial than smaller indexes. You must always weigh the benefits of reusing an index against the cost of it being less specific. Here are some ways you may make an index more versatile, but remember most of these come at a cost.

• Use additional columns in the INCLUDE.
• Remove the WHERE clause in the index or move that column to the INCLUDE or key columns.
• Move a column from the INCLUDE to a key column when 2 or more queries share the need for key columns for the first few columns, but additional columns could benefit additional queries.

When should I use one?

Like most indexes, you must weigh read savings against write costs. Covering indexes often contain more data with more key columns than your average index, so the effects are exaggerated.

Most servers are read-heavy and can benefit from generous indexing. Consider how heavy the write load on your server is. Consider when writes typically occur. With reporting servers, they tend to be very read-heavy, and the writes often happen in off-hours when performance matters less, so you can index boldly. Consider the importance of specific operations in terms of performance. Often a particular read or write operation needs to be quick for various reasons, even if the overall server performance needs to make compromises.

Consider using a covering index in the following scenarios.

• There is a query with unacceptable performance after other optimizations.
• Your server has relaxed requirements for write performance, such as a reporting server.
• Your server performs reads much more often than writes.

A covering index may cost more than it is worth in the following scenarios.

• Write performance on the objects involved is especially important.
• Write performance is already slow on your server.
• The tables involved are written into very often, such as when triggers are involved.
• The size of the index must be increased dramatically to make it covering.
• The index is very specific or it would otherwise only be used occasionally.

Real life tips

In the real world, a covering index can be fragile. Small changes to your queries or your data can cause an index to not be covering, or to not be used at all. As we saw in the example above, a very small change to an index can improve performance dramatically. Unfortunately, the opposite is true.

In our example, if someone decides to return an extra column not included in our covering index, the query may start running 184x slower again. Because of this, it can be helpful to comment your code when a covering index is involved to cue you to check whether changes you make affect it, and review your index usage regularly to catch any changes you miss otherwise.