Logical reading to search by unique clustered index

Question

To define a table

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

It contains 21 lines (7 for each of the AccountId values 4,6,7 ).

It has 1 page with root and 4 pages of sheet

 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 entries on these pages

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

Inquiry

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

Gives the following IO statistics

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

Why?

I thought that for each search, he would look for the first page that could potentially contain this value, and then (if necessary) continue on the linked list until he found the first line, not equal to the desired value.

However, this adds up to 10 access pages.

 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 explains the extra 3?

Full script to play

 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 provide an answer in the form of an answer, and not as a discussion in the comments ...

Additional readings arise due to the forward reading mechanism. This scans the parent page-level pages if it needs to issue asynchronous I / O to bring the page-level pages to the buffer cache so that they are ready when a range search reaches them.

You can use trace flag 652 to turn off the mechanism (on the server) and verify that the number of reads is now exactly 10 as expected.

Answer 2

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

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