0

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.

1

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.

  • Thanks for the answer (also answers my follow up of when to choose which if this was expected). Just so I understand the difference between how they are treated for update and select. If the nonclustered index is covering, is the number of logical page reads for a select effectively always <= the number of pages in the index, because the data is included on the index pages? But because this is an update, logical reads will be # of rows to update * (height of the tree + 1)? – BroSlow Jun 20 '18 at 0:57
  • Not exactly the number of pages read depends on a number of things. In this example it mostly relates to whether it is a seek or a scan (remember, in both examples you executed an update). SQL will try to find the most efficient way of retrieving rows and takes into considerations things like table/index statistics, uniqueness of values, number of rows to be retrieved versus the number of all rows in a table. For selecting data, SQL will use the best suiting indexes that exists on that table. An update on the other hand needs to affect data page + all index pages with the updated column. – Błażej Ciesielski Jun 20 '18 at 13:28
  • This is a broader topic and it is not possible to explain all the details in a short post. Try looking at some articles, books and videos about indexing and how sql optimizer works. Here's one good free ebook from Benjamin Nevarez red-gate.com/library/inside-the-sql-server-query-optimizer – Błażej Ciesielski Jun 20 '18 at 13:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.