Sql-server – Update using NonClustered index results in large number of logical reads

sql serversql-server-2012

While doing some debugging of a frequently called stored procedure, I was testing adding a column to one of the nonclustered indexes to make it cover, but noticed that doing so would dramatically increase the number of logical reads the stored procedure performed (compared to when it used a clustered index scan). Running multiple iterations of the same stored procedure in parallel also resulted in worse CPU time. I was able to reproduce this behavior with multiple builds of SQL Server 2012. Can anyone explain why this is happening?

Example table

CREATE TABLE dbo.TestTable (
    ID INT PRIMARY KEY IDENTITY,
    Condition BIT NOT NULL,
    OtherColumn INT NOT NULL
);

CREATE NONCLUSTERED INDEX NC ON dbo.TestTable(Condition);

-- Generate 1000 rows with Condition = 0 and 1000 with Condition = 1
DECLARE @i INT = 0;
WHILE @i < 1000
BEGIN
    INSERT INTO dbo.TestTable(Condition, OtherColumn) VALUES (0, @i);
    INSERT INTO dbo.TestTable(Condition, OtherColumn) VALUES (1, @i);
    SET @i = @i + 1;
END;

Example update

UPDATE dbo.TestTable SET OtherColumn = 1 WHERE Condition = 1;

Results with the nonclustered index enabled (nonclustered index seek) – Paste The Plan

Table 'TestTable'. Scan count 1, logical reads 2005, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.

Results with the nonclustered index disabled (clustered index scan) – Paste The Plan

Table 'TestTable'. Scan count 1, logical reads 7, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.

Best Answer

The difference is due to: Seek(nonclustered) vs. Scan(clustered).

Some (oversimplified) explanation:

When performing seek, SQL Server must lookup every row. To find that row it has to travel down the index tree and access a page on every level. In your case, with 2000 rows, i believe there is one index level and a data level. This means that for every of 1000 rows, SQL has to access 2 pages (row1 index->data.. row2 index->data ...) which explains ~2k logical reads.

The scan on the other hand works in a way that it looks up the first row and then follows all rows on the page and all the next pages sequentially. So it would look something like that : root page > index > find 1st data page > 2nd data page ...

This does not mean that Seek is worse and Scan is better (usually the opposite), because more often in the real world would you have large tables and retrieve/update only small portions of it (which seeks are good at). With operations on a large percentage of the table scans will often be more efficient.

Related Question