Clustered index data access

I am trying to understand how SQL Server access data from the clustered index. My understanding is when the table has a clustered index SQL should be able to seek into the single page that holds the record using the seek predicate.

However, my test showing me when the query is executed it loads a few more data pages.
Setup

CREATE TABLE t2(id INT IDENTITY PRIMARY KEY CLUSTERED,col2 VARCHAR(500),col3 VARCHAR(500));

INSERT INTO [dbo].[t2]([col2],[col3])
SELECT TOP 10010 REPLICATE('z',490),REPLICATE('*',490)
FROM sys.all_columns c1,
     sys.all_columns c2

The following query

select *
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.t2'),DEFAULT,null,'DETAILED');

shows the output as

I then cleared the cache with

CHECKPOINT 

GO

DBCC DROPCLEANBUFFERS

And ran the following SELECT seeking into a single row

SELECT  [fplc].*,[t2].[col3] FROM [dbo].[t2] AS [t2] 
CROSS APPLY sys.[fn_PhysLocCracker](%%physloc%%) AS [fplc]
WHERE id=4582

The above query tells me the record is located on the page 1061

I use the code below check how many pages have been loaded into the buffer to get the result of my SELECT

SELECT buffers.* FROM sys.dm_os_buffer_descriptors  buffers
INNER JOIN sys.allocation_units AS au
    ON au.[allocation_unit_id] = buffers.[allocation_unit_id]
INNER JOIN sys.partitions AS p
    ON au.[container_id] = p.[partition_id]
INNER JOIN sys.indexes AS i
    ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
WHERE p.[object_id] > 100
and [database_id] = DB_ID () AND i.[object_id]=OBJECT_ID('t2')
 ORDER BY [page_level] desc

Why SQL is loading all the pages marked in green? Are the pages loaded by READ AHEAD ?

This is explained in The Read Ahead that doesn’t count as Read Ahead


what I wanted to talk about in this post, was another prefetching
mechanism that can be triggered by any query when SQL is running any
of the editions considered Enterprise (i.e. Developer, Evaluation, and
Enterprise itself). The aim of this optimization is to warm up the
cache as quickly as possible. To do so, the buffer pool converts any
request to read a single page from disk into a request that will read
the whole extent containing the page initially requested.

In your case you ran this after clearing the buffer cache with DBCC DROPCLEANBUFFERS. So SQL Server is in the state that it would try and warm up the cache quickly.

You needed to read three pages to perform the seek (one at each level of the index). These were pages 1111, 1382 and 1061.

So you ended up bringing in three entire extents with page numbers 1056 - 1063, 1104 - 1111 and 1376 - 1383.

If you do the same experiment against some other SKU than mentioned in the quote (and traceflag 840 is not enabled) you should see your expected three pages (below was against Express LocalDB)

StackExchange Database Administrators Q#266121, answer score: 5