Database Reference
In-Depth Information
Note
Chapter 20 covers blocking in more depth.
To understand how the cost of an UPDATE statement that modifies only a clustered key column is increased by
the presence of nonclustered indexes on the table, consider the following example. The Sales.SpecialOfferProduct
table has a composite clustered index on the primary key, which is also the foreign key from two different tables; this
is a classic many-to-many join. In this example, I update one of the two columns using the following statement
(note the use of the transaction to keep the test data intact):
BEGIN TRAN
SET STATISTICS IO ON;
UPDATE Sales.SpecialOfferProduct
SET ProductID = 345
WHERE SpecialOfferID = 1
AND ProductID = 720;
SET STATISTICS IO OFF;
ROLLBACK TRAN
The STATISTICS IO output shows the reads necessary.
Table 'Product'. Scan count 0, logical reads 2
Table 'SalesOrderDetail'. Scan count 1, logical reads 1246
Table 'SpecialOfferProduct'. Scan count 0, logical reads 15
If you added a nonclustered index to the table, you would see the reads increase, as shown here:
CREATE NONCLUSTERED INDEX ixTest
ON Sales.SpecialOfferProduct (ModifiedDate);
When you run the same query again, the output of STATISTICS IO changes for the SpecialOfferProduct table.
Table 'Product'. Scan count 0, logical reads 2
Table 'SalesOrderDetail'. Scan count 1, logical reads 1246
Table 'SpecialOfferProduct'. Scan count 0, logical reads 19
The number of reads caused by the update of the clustered index is increased with the addition of the
nonclustered index. Be sure to drop the index.
DROP INDEX Sales.SpecialOfferProduct.ixTest;
Wide Keys
Since all nonclustered indexes hold the clustered keys as their row locator, for performance reasons you should
avoid creating a clustered index on a very wide column (or columns) or on too many columns. As explained in the
preceding section, a clustered index must be as narrow as possible.
 
 
Search WWH ::




Custom Search