Index Architecture and Behavior - SQL Server Query Performance Tuning - page 114

WITH Nums
AS (SELECT TOP (10000)
ROW_NUMBER() OVER (ORDER BY (SELECT 1
)) AS n
FROM master.sys.all_columns ac1
CROSS JOIN master.sys.all_columns ac2
)
INSERT INTO dbo.Test1
(C1, C2, C3)
SELECT n,
n,
'C3'
FROM Nums;
Run an UPDATE statement, like so:
UPDATE dbo.Test1
SET C1 = 1,
C2 = 1
WHERE C2 = 1;
Then the number of logical reads reported by SET STATISTICS I0 is as follows:
Table 'Test1'. Scan count 1, logical reads 29
Add an index on column cl , like so:
CREATE CLUSTERED INDEX iTest
ON dbo.Test1(C1);
Then the resultant number of logical reads for the same UPDATE statement increases from 29 to 42 but also has
added a worktable with an additional 5 reads for a total of 47:
Table 'Test1'. Scan count 1, logical reads 42
Table 'Worktable'. Scan count 1, logical reads 5
The number of reads goes up because it was necessary to rearrange the data in order to store it in the correct
order within the clustered index, increasing the number of reads beyond what was necessary for a heap table to just
add the data to the end of the existing storage.
Even though it is true that the amount of overhead required to maintain indexes increases for data manipulation
queries, be aware that SQL Server must first find a row before it can update or delete it; therefore, indexes can be
helpful for UPDATE and DELETE statements with necessary WHERE clauses. The increased efficiency in using the index
to locate a row usually offsets the extra overhead needed to update the indexes, unless the table has a lot of indexes.
Further, the vast majority of systems are read heavy, meaning they have a lot more data being retrieved than is being
inserted or modified.
To understand how an index can benefit even data modification queries, let's build on the example. Create another
index on table tl . This time, create the index on column c2 referred to in the WHERE clause of the UPDATE statement.
CREATE INDEX iTest2
ON dbo.Test1(C2);