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 for the query. A covering index, which is a form of a composite index, includes all of the columns referenced in the 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 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 may be beneficial. [2000, 2005, 2008] Updated 1-29-2009
*****
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. [2000, 2005, 2008] Updated 1-29-2009
*****
If you want to create a covering index, consider piggybacking on already existing indexes. 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. [2000, 2005, 2008] Updated 1-29-2009
*****
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 often 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 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. [2000, 2005, 2008] Updated 1-29-2009
*****
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 (RID or Key) 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. [2000, 2005, 2008] Updated 1-29-2009
*****
If you are using SQL Server 2005 or 2008, you can use an included index instead of a composite index to create a covering index. The advantage of an included index over a composite covering index is that it is lighter weight and can offer better performance. [2005, 2008] Updated 1-29-2009
]]>