T-SQL Tuesday #114 – An Unsolved SQL Puzzle

T-SQL Tuesday

This week’s T-SQL Tuesday invitation is all about puzzles. I’ve got an accidental puzzle that I’ve never quite solved, from one of my demos. I’m sure the answer will be a “Well duh!” moment.

I give presentations on SQL Server execution plans. As part of that, I like to show that if you pull a single row from a heap, it has to read everything. As part of that demo, I try to push everything out of memory by disabling readahead reads, taking a checkpoint, and dropping clean buffers. But for some reason… it never quite works!

IF NOT EXISTS ( SELECT  *
FROM    sys.schemas
WHERE   name = N'Demo' )
EXEC('CREATE SCHEMA [Demo] AUTHORIZATION [dbo]');
GO

IF OBJECT_ID('Adventureworks2014.Demo.Person', 'U') IS NOT NULL
BEGIN
DROP TABLE AdventureWorks2014.Demo.Person;
END

SET STATISTICS IO ON

SELECT *
INTO [AdventureWorks2014].[Demo].[Person]
FROM [AdventureWorks2014].[Person].[Person]

DBCC TRACEON (652,-1);

CHECKPOINT
DBCC DROPCLEANBUFFERS

SELECT * FROM [AdventureWorks2014].[Demo].[Person]
WHERE BusinessEntityID = 25

You can see here that it shows 3,808 logical reads, but 5 physical reads.

Screen shot of statistics IO showing the number of reads.

I’m sure there is some simple way to force it to do all of the physical reads, but I have yet to figure it out. Or it may be that I’m misunderstanding something and physical reads are the only pages used. But when I look at the execution plan, it says it read all of the rows.

I’d love to get an answer to this puzzle. I’m sure it’s something simple.

Update: Andy G. asked if maybe the issue is I’m not using all the rows. Here I tried a heap of a single row, and I get 1 logical read and 0 physical reads.

Table 'Person'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

3 thoughts on “T-SQL Tuesday #114 – An Unsolved SQL Puzzle”

  1. I don’t think think it’s all that simple. I collected callstacks while running your demo query. It looks like, even though read-ahead is disabled, SQL Server still uses “prefetching” to reduce the number of physical reads being done when scanning a heap.

    This might be true anytime an allocation order scan is being done. I see the same pattern happen if I add a clustered index to the table. I can “SELECT *” from the table with read-ahead disabled, and I get equal physical and logical reads. If I add a “WITH (TABLOCK)” hint to force an allocation order scan, physical reads drop (as if read-ahead were not disabled).

    Interesting post, thanks Eugene!

  2. Gene – how big is your actual Person table?

    Just thinking out loud…if there were only five pages worth of data, would you only get five physical reads for your resultset?

    Consider this possibility:

    Row #1 – Physical Read of Page #1
    Row #2 (still on Page #1) – Logical Read of Page #1
    Row #3 (still on Page #1) – Logical Read of Page #1
    …
    Row #X – Physical Read of Page #2
    Row #X+1 (still on Page #2) – Logical Read of Page #2
    Row #X+2 (still on Page #2) – Logical Read of Page #2
    —
    etc.

    Like I said – just thinking out loud…you may be able to test this by varying the size of your table by filtering your original SELECT…INTO to make a smaller table.

Comments are closed.