3

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

enter image description here

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 ? enter image description here

2
  • If the table is small, it might decide to scan the table as opposed to seeking because it would be faster. Apr 29 '20 at 18:34
  • @TonyHinkle - That is often stated but I've never seen SQL Server decide to convert a seekable predicate on a covering index to a scan on the same index regardless if the table has no rows at all Apr 29 '20 at 19:09
5

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)

enter image description here

2
  • Thanks Martin.. I appreciate your support. I have another question and apologies if its a dumb question. So,SQL did additional pages to warm-up the cache. If the table has ROW_OVERFLOW type data, wouldn't this additional read cause unnecessary IO and cost memory? Apr 29 '20 at 19:36
  • 1
    It didn't do additional reads. It just did 64KB reads instead of 8KB reads. It only does this ramp up when it is using less memory than target. With row overflow pages reading them in speculatively could still be useful. They may never get used and eventually get evicted under the LRU policy but that is the same for the data pages that are brought in speculatively. Apr 29 '20 at 19:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.