Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Index Covering Boosts SQL Server Query Performance

Index covering is a quick and easy solution to many query performance problems. By just adding a column or two to an index you can significantly boost a query's performance.


advertisement
reating a non-clustered index that contains all the columns used in a SQL query, a technique called index covering, is a quick and easy solution to many query performance problems. Sometimes just adding a column or two to an index can really boost the query's performance. Check out a couple of all-too-common scenarios where applying index covering can speed up the queries in your Microsoft SQL Server database.

Scenario 1: Speed Up a Query for a Table with a Clustered Index

Consider a very typical scenario: you have a table ORDERS with a clustered index on CUSTOMER_ID column. You need to speed up a select query quickly:

SELECT ORDER_DATE, SUM(AMOUNT) FROM ORDERS GROUP BY ORDER_DATE



The query's execution plan is quite simple: the database engine scans the whole clustered index, and then it sorts the intermediate result set to satisfy the GROUP BY clause. Can a non-clustered index speed up the query? Definitely. Just create a non-clustered index that contains all the columns used in the query:

CREATE INDEX order_amt ON dbo.ORDERS(ORDER_DATE, AMOUNT)

Rerun the query and you'll find it runs very fast. Why? First, look at the execution plan: the query accesses only the index order_amt; it doesn't touch the table at all. In fact, there is no need to access the table because all the columns necessary to satisfy the query are already stored in the index. This is index covering. The index is much smaller than the table, which is why the query read many fewer pages. Also, the index entries are already stored in the necessary order, so there is no need to sort the intermediate result set.

Not only does the plan look good, but the real execution cost of the query after creating the index also is much cheaper:

logical reads 1919, (snip) CPU time = 875 ms

This is significantly less than the real execution costs before the index was created:

logical reads 8365, (snip) CPU time = 2015 ms

As you can see, the query ran much faster because it read fewer pages and did not sort intermediate results.

Now that the query runs fast enough, it is time to consider the price you paid for speeding it up. The index uses up some storage. Also, modifying the table might become slower. If having this query return as fast as possible is a high priority for you, you can stop at this point. Depending on your circumstances, however, you might want to consider the impact of this new index on the overall performance of the system. You can use the Index Tuning Wizard in SQL Server to validate your quick fix against a realistic workload, but that is beyond the scope of this article. (Microsoft TechNet offers instructions for Index Tuning Wizard use in SQL Server 2005 and SQL Server 7.0.)



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap