Update using NonClustered index results in large number of logical reads?

by BroSlow   Last Updated June 19, 2018 23:06 PM

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.



Related Questions



Quorom Configuration for 3 Node Windows cluster

Updated October 16, 2018 08:06 AM