Extra logical read in a clustered index seek - by Quassnoi

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
0
Sign in
to vote
ID 667095 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 5/6/2011 6:40:21 AM
Access Restriction Public

Description

When seeking for values in a clustered non-unique index, an extra logical read is performed.

Steps to reproduce:

CREATE TABLE accounts
        (
        id INT NOT NULL,
        filler CHAR(1000)
        )

CREATE CLUSTERED INDEX
        ix_accounts_id
ON      accounts (id)

INSERT
INTO    accounts (id)
SELECT  c
FROM    (
        VALUES
        (4),
        (6),
        (7)
        ) vals (c)
CROSS JOIN
        (
        VALUES
        (1),
        (2),
        (3),
        (4),
        (5),
        (6),
        (7)
        ) nums (n)

SELECT  id, p.*
FROM    accounts
CROSS APPLY
        sys.fn_physloccracker(%%physloc%%) p

SET STATISTICS IO ON;
SELECT TOP 6 * FROM accounts WHERE id = 4
SELECT TOP 7 * FROM accounts WHERE id = 4
SELECT TOP 8 * FROM accounts WHERE id = 4
SET STATISTICS IO OFF;

The output follows:


(6 row(s) affected)
Table 'accounts'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(7 row(s) affected)
Table 'accounts'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(7 row(s) affected)
Table 'accounts'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

As we can see, there are 7 records with id = 4, all in a single page (we can see it in the output of sys.fn_physloccracker).

When we select TOP 6 records, there are 2 logical reads which is expected: one read of the root page (with the keys) and one read of the leaf page (with the data).

However, when we select TOP 7 records, an extra logical read is performed for no obvious reason (since all 7 records are in one page, TOP 7 should be satisfied by this page only).

The same holds for TOP 8 records. There are but 7 records with id = 4 in the page (as well as in the whole table), so an extra logical read from the next page is justified, which should amount the number of reads to 3. However, for some reason there are 4 reads, with an extra logical read being performed again for no obvious reason.

Not sure it's a bug, however, it would be interesting to know the reason for this extra read as well as having it documented.
Sign in to post a comment.
Posted by Microsoft on 1/25/2012 at 4:36 PM
Thank you for submitting this suggestion, but given its priority relative to the many other enhancements we are considering, it is unlikely that we will actually complete it. As such, we are closing this suggestion as “won’t fix”. If you feel that this is worth reconsidering, feel free to respond to this message and we will take another look.

Thank you,
Eric Hanson
Program Manager
SQL Server Query Processing
Posted by Microsoft on 5/10/2011 at 10:41 AM
Thanks for the feedback. We'll consider this for a future release.

Best regards,
Eric Hanson
Program Manager
Microsoft SQL Server, Query Processing