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.
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