Covering Index
Today I decided to talk about covering indexes. I was pleased to find there has been a lot of recent activity on covering indexes posted here at www.sswug.org. Simply go to our site and type “Covering Index” in the search box. You’ll find many posts.
For our Accidental DBA friends, or those new to relational databases, a Covering Index is an index that acts in place of a table. When a database engine returns data using an index, if all of the requirements of the request can be fulfilled by data stored in the index, it will simply use the index in place of the table. This technique is called “Covering” because the index covers all of the query requirements.
Most engines have a restriction on length of the bytes making up a row in an index. Often this is implemented by excluding certain data types from being index keys, and restricting the number of columns included in a key.
With SQL Server 2008, Microsoft released an additional clause to the create index statement; “INCLUDE”.
Using INCLUDE you may specify additional columns that are included in the index, but are not part of the index key. This index performs like a clustered index when acting as a covering index. The INCLUDE columns are stored in the leaf pages of the index itself. This means that it does not have to read the index, get the pointer to the location where the data is stored, and then get the desired data. In this case it simply reads the index, and the desired data is in the same physical location of the disk.
Covering indexes are used frequently for ETL processes to optimize data retrieval. Often they are created at the beginning of ETL and then dropped. Many batch processes benefit from the overhead of creating covering indexes in preparation for work. Dropping the index when the batch processing is complete allows daily work to perform more quickly because the covering index does not have to be maintained.
Here is a real world example demonstrating the benefit of creating and dropping an index. I have an archive table I maintain on a slow drive for the unlikely event someone wished data that had been purged. This week I used this capability for the first time in two years. It turned out I needed to restore 33 records out of an archive table containing just over 40 million rows. I decided to run a comparison for methods retrieving these records.
First I ran a query selecting these 33 records with no index on the table. The query simply performed a table scan looking for the desired rows. This query ran for 38 minutes before returning my records.
Second, I created a non clustered index. I didn’t create a covering index in this case, since I needed all the columns anyway. I didn’t create a clustered index since I didn’t want to re-organized all the data in the table. I simply created an index on the two columns I needed for filtering. It took 2.5 Minutes to create the index.
Then I ran my query to extract my 33 records. The query took .5 seconds. The net time was just over 2.5 minutes compared to 38 minutes.
This demonstrates how creating an index prior to batch processing, and later dropping the index, may improve performance for Batch processing jobs. Covering indexes behave in a similar manner.
When you have consistent queries that benefit from a covering index, simply leave the index in place, and take the additional overhead for maintaining the index as the table is modified.
There are lots of index management techniques available to you on www.sswug.org. Be sure to take a look!
Send your index management tips and techniques to btaylor@sswug.org and share with our readers.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
SQL Server 2008 Transact-SQL Optimization Tips (Part 1)
In this article, you can find some helpful SQL Server 2008 Transact-SQL tips.
Featured White Paper(s)
Query Tuning Strategies for Microsoft SQL Server
Written by Quest Software
When you’re looking for a reliable tool to diagnose … (read more)