Benchmarking SQL Server 2005 Covering IndexesBy : Dinesh AsankaApr 10, 2007 What is a Covering Index?A covering index is a form of a non-clustered co......
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.
5/5 首页 上一页 3 4 5 |
