Benchmarking SQL Server 2005 Covering Indexes

Results

Following are the results gathered from the Execution Plans of each query. Execution time is in seconds, and CPU Cost and I/O Cost were calculated/measured from the Execution plans. All the values for the parameters were noted down for three cases as mentioned above against the same SELECT queries and the same INSERT queries.

Analysis

Case 1: Without Any Indexes

Where there are no indexes on the table, there is no other way of returning data except to perform a table scan. Your query will run through the entire table, row by row, to fetch the record(s) that matches your query conditions.

Needless to say, this is an extremely insufficient way of fetching data from your tables. You can see that it took twelve seconds to fetch the data.

Case 2: With Non-Covering Indexes

Now we add two non-clustered indexes to these two columns—ItemCode and Order Number—in our table. This time, the query optimizer uses a RID Lookup to get this information. A RID Lookup is a bookmark lookup on a heap using a supplied row identifier (RID). The Argument column contains the bookmark label used to look up the row in the table and the name of the table in which the row is looked up. As you can see below, the RID Lookup has taken 51% of the entire cost.

However, because of the usage of indexes, this time the script was executed in 4 seconds, which is 300% faster than the normal. CPU Costs were reduced by 81%, while I/O Costs were reduced by 64%.

However, INSERT statements now take more time and resources than when there was no index on the table.

Case 3: With Covering Indexes

Our next scenario includes a covering index. As a covering index includes all the information for the query, SQL Server will retrieve the data faster and with less resource utilization. In addition, with a covering index, you won't get as complex an Execution Plan.

With a covering index, the execution time of the SELECT query has been reduced to 3 seconds. When you compare this result to not using any indexes, you can see that it has an improvement of 400%, while with the non-clustered index, it has a 75% improvement in performance. CPU cost and I/O Cost also improved, which means that after the covering index was introduced, the query uses fewer resources for SELECT queries.

Like in the previous case, INSERTs have taken more time, as well as additional resources. You can see INSERT statement execution time has gone up for 5 seconds, as compared to the 1-second timeframe when no indexes were added to the table.

Conclusion

As the above statistics suggest, covering indexes offer both advantages and disadvantages. It is your job as the DBA to determine whether the advantages outweigh the disadvantages, and whether implementing a covering index is best for your specific needs.