Logical reads for seeks on a non unique clustered index

Question

For the table definition

CREATE  TABLE Accounts
(
AccountID INT ,
Filler CHAR(1000)
)

Containing 21 rows (7 for each of the AccountId values 4,6,7).

It has 1 root page and 4 leaf pages

index_depth page_count           index_level
----------- -------------------- -----------
2           4                    0
2           1                    1

The root page looks like

FileId      PageId      ROW         LEVEL       ChildFieldId ChildPageId AccountId (KEY) UNIQUIFIER (KEY) KeyHashValue
----------- ----------- ----------- ----------- ------------ ----------- --------------- ---------------- ------------------------------
1           121         0           1           1            119         NULL            NULL             NULL
1           121         1           1           1            151         6               0                NULL
1           121         2           1           1            175         6               3                NULL
1           121         3           1           1            215         7               1                NULL

The actual distribution of AccountId records over these pages is

AccountID   page_id     Num
----------- ----------- -----------
4           119         7
6           151         3
6           175         4
7           175         1
7           215         6

The Query

SELECT AccountID 
FROM Accounts 
WHERE AccountID IN (4,6,7) 

Gives the following IO stats

Table 'Accounts'. Scan count 3, logical reads 13

Why?

I thought for each seek it would seek into the first page that might potentially contain that value and then (if necessary) continue along the linked list until it found the first row not equal to the seeked value.

However that only adds up to 10 page accesses

4)  Root Page -> Page 119 -> Page 151             (Page 151 Contains a 6 so should stop)
6)  Root Page -> Page 119 -> Page 151 -> Page 175 (Page 175 Contains a 7 so should stop)
7)  Root Page -> Page 175 -> Page 215             (No more pages)      

So what accounts for the additional 3?

Full script to reproduce

USE tempdb

SET NOCOUNT ON;

CREATE  TABLE Accounts
(
AccountID INT ,
Filler CHAR(1000)
)

CREATE CLUSTERED INDEX ix ON Accounts(AccountID)


INSERT INTO Accounts(AccountID)
SELECT C
FROM (SELECT 4 UNION ALL SELECT 6 UNION ALL SELECT 7) Vals(C)
CROSS JOIN (SELECT TOP (7) 1 FROM master..spt_values) T(X)

DECLARE @AccountID INT

SET STATISTICS IO ON
SELECT @AccountID=AccountID FROM Accounts WHERE AccountID IN (4,6,7) 
SET STATISTICS IO OFF

SELECT index_depth,page_count,index_level
FROM
sys.dm_db_index_physical_stats (2,OBJECT_ID('Accounts'), DEFAULT,DEFAULT, 'DETAILED')

SELECT AccountID, P.page_id, COUNT(*) AS Num
FROM Accounts
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) P
GROUP BY AccountID, P.page_id
ORDER BY AccountID, P.page_id

DECLARE @index_info  TABLE
(PageFID  VARCHAR(10), 
  PagePID VARCHAR(10),   
  IAMFID   TINYINT, 
  IAMPID  INT, 
  ObjectID  INT,
  IndexID  TINYINT,
  PartitionNumber TINYINT,
  PartitionID BIGINT,
  iam_chain_type  VARCHAR(30),    
  PageType  TINYINT, 
  IndexLevel  TINYINT,
  NextPageFID  TINYINT,
  NextPagePID  INT,
  PrevPageFID  TINYINT,
  PrevPagePID INT, 
  PRIMARY KEY (PageFID, PagePID));
  
INSERT INTO @index_info 
    EXEC ('DBCC IND ( tempdb, Accounts, -1)'  ); 

DECLARE @DynSQL NVARCHAR(MAX) = 'DBCC TRACEON (3604);'
SELECT @DynSQL = @DynSQL + '
DBCC PAGE(tempdb, ' + PageFID + ', ' + PagePID + ', 3); '
FROM @index_info     
WHERE IndexLevel = 1

SET @DynSQL = @DynSQL + '
DBCC TRACEOFF(3604); '

CREATE TABLE #index_l1_info  
(FileId  INT, 
  PageId INT,   
  ROW   INT, 
  LEVEL  INT, 
  ChildFieldId  INT,
  ChildPageId INT,
  [AccountId (KEY)] INT,
  [UNIQUIFIER (KEY)] INT,
  KeyHashValue  VARCHAR(30));
  
INSERT INTO #index_l1_info  
EXEC(@DynSQL)


SELECT *
FROM #index_l1_info

DROP TABLE #index_l1_info
DROP TABLE Accounts

Answer 1

Just to supply the answer in answer form rather than as discussion in the comments...

The additional reads arise due to the read ahead mechanism. This scans the parent pages of the leaf level in case it needs to issue an asynchronous IO to bring the leaf level pages into the buffer cache so they are ready when the range seek reaches them.

It is possible to use trace flag 652 to disable the mechanism (server wide) and verify that the number of reads is now exactly 10 as expected.

Answer 2

From what I see from the output of DBCC IND, there is 1 root page (type = 10), 1 key page (type = 2) and four leaf pages (type = 1), total of 6 pages.

So each scan goes as root -> key -> leaf -> … -> final leaf which gives 4 reads for 4 and 7 and 5 reads for 6, total 4 + 4 + 5 = 13.