Microsoft® SQL Server 2008 R2 Unleashed by Ray Rankins & Paul Bertucci & Chris Gallelli & Alex T. Silverstein

Author:Ray Rankins & Paul Bertucci & Chris Gallelli & Alex T. Silverstein
Language: eng
Format: epub
Publisher: Sams
Published: 2011-06-14T16:00:00+00:00

Explicitly adding additional columns to nonclustered indexes to promote the occurrence of index covering has historically been a common method of improving query response time. Consider the following query:

If you create an index on only the price column, SQL Server can find the rows in the index where price is between $10 and $20, but it has to access the data rows to retrieve royalty. With 100 rows in the range, the worst-case I/O cost to retrieve the data rows would be as follows:

Number of index levels

+ Number of index pages to find the 100 matching rows

+ (100 × Number of pages per lookup via the row locator)

If the royalty column were added to the index on the price column, SQL Server could scan the index to retrieve the results instead of having to perform the lookups via the row locator against the table, resulting in faster query response. The I/O cost using index covering would be lower, as follows:

Number of index levels

+ Number of index pages to scan to find the 100 matching rows

If you are considering padding your indexes to take advantage of index covering, beware of making an index too wide. As index row width approaches data row width, the benefits of covering are lost as the number of pages in the leaf level increases. As the number of leaf-level index pages approaches the number of pages in the table, the number of index levels also increases, increasing the I/O cost of using the index to locate data.

You should also avoid adding to the index columns that are frequently updated. Remember that any changes to the columns in the data rows cascade into the indexes as well. This increases the index maintenance overhead, which can adversely affect update performance.

As an alternative to adding columns to the nonclustered index key to encourage index covering, you might want to consider taking advantage of the included columns feature in SQL Server 2008.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.