Tips on Optimizing Covering Indexes

If you have to use a non-clustered index (because your single clustered index can be used better elsewhere in a table), and if you know that your application will be performing the same query over and over on the same table, consider creating a covering index on the table. A covering index, which is a form of a composite index, includes all of the columns referenced in SELECT, JOIN, and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL Server doesn't have to look up the actual data in the table, reducing logical and/or physical I/O, and boosting performance.

On the other hand, if the covering index gets too big (has too many columns), this could actually increase I/O and degrade performance. Generally, when creating covering indexes, follow these guidelines:

If the query or queries you run using the covering index are seldom run, then the overhead of the covering index may outweigh the benefits it provides.

The covering index should not add significantly to the size of the key. If it does, then it its use may not outweigh the benefits it provides.

The covering index must include all columns found in the SELECT list, the JOIN clause, and the WHERE clause.


One clue to whether or not a query can be helped by a covering index is if the execution plan of the query uses a Bookmark Lookup. If it does, then adding a covering index is often beneficial.


************

If a query makes use of aggregates, and it is run often, then you may want to consider adding a covering index for this query. Non-clustered indexes include a row with an index key value for every row in a table. Because of this, SQL Server can use these entries in the index's leaf level to perform aggregate calculations. This means that SQL Server does not have to go to the actual table to perform the aggregate calculations, which can boost performance.


***************

If you want to create a covering index, if possible, try to piggyback on already existing indexes that exist for the table. For example, say you need a covering index for columns c1 and c3. If you already have an index on column c1, instead of creating a new covering index, change the index on c1 to be a composite index on c1 and c3. Anytime you can prevent indexing the same column more than once, the less I/O overhead SQL Server will experience, and the faster performance will be.


*************

How can you tell if a covering index you created is actually being used by the Query Optimizer? You can find this out by turning on and viewing the graphical execution plan output. If you see this phrase, "Scanning a non-clustered index entirely or only a range," this means that the query optimizer was able to cover that particular query with an index.


*************

An alternative to creating covering indexes on non-clustered indexes is to let SQL Server create the covering indexes for you automatically. Here's how this works.

The query optimizer can perform what is called index intersection. This allows the optimizer to consider multiple indexes from a table, build a hash table based on the multiple indexes, and then use the hash table to reduce I/O for the query. In effect, the hash table becomes a covering index for the query.

Although index intersection is performed automatically by the query optimizer, you can help it along by creating single column, non-clustered indexes on all the columns in a table that will be queried frequently. This provides the query optimizer with the data it needs to create covering indexes as needed, on the fly.


**************


One way to help determine if a covering index could help a query's performance is to create a graphical query execution plan in Query Analyzer or Management Studio of the query in question and see if there are any Bookmark Lookups being performed. Essentially, a Bookmark Lookup is telling you that the Query Processor had to look up the row columns it needs from a table or a clustered index, instead of being able to read it directly from a non-clustered index. Bookmark Lookups can reduce query performance because they produce extra disk I/O to retrieve the column data.

One way to avoid a Bookmark Lookup is to create a covering index. This way, all the columns from the query are available directly from the non-clustered index, which means that Bookmark Lookups are unnecessary, which reduces disk I/O and helps to boost performance.