Is Advanced Scanning not effective because there is a problem with my testing method?

Question

I expect the output of the two to be different. The session output of 2552 read-ahead reads should be a smaller number than 100000000 because it does "merry-go-round scanning".

But the actual result is that it outputs 100000000. Why is that?

Thank you for your help

--session 1
set nocount on
create table test(id int)

begin tran
declare @i int = 1
while @i <=100000000
begin
insert into test values(@i)
set @i = @i+1
end
commit

checkpoint
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS 

--session 2
set statistics io on
WAITFOR DELAY '00:00:00.2'
declare @time datetime2 = getdate()
print @time
declare @i int
select @i = id from test 
print @i
set @time = getdate()
print @time

--session 3
set statistics io on
declare @time datetime2 = getdate()
print @time
declare @i int
select @i = id from test
print @i
set @time = getdate()
print @time

Answer 1

Merry-go-round scanning is one of these illusionary creatures that people have heard of, but far from all have been able to actually detect.

In your case, SQL Server could be smart and say "Know what, logically, there is no need to start over from the beginning".

I tried rearranging the query like this:

set statistics io on
WAITFOR DELAY '00:00:00.2'
declare @time datetime2 = getdate()
print @time
declare @i int
; with numbering AS (
   select id, rowno = row_number() OVER(ORDER BY (SELECT 1)) from test 
)
SELECT id FROM numbering WHERE rowno = 10000000 
print @i
set @time = getdate()
print @time

My lack of patience also compelled me to reduce the number of rows by slashing a zero.

My feeble attempt to run them in parallel did succeed very well, I got back 50382 in both windows.

One way to set up test like this is to use application locks. First take an exclusive application lock in one window and go other to the other window, where you take a shared application lock on the same resource and directly after this you run your test. Go back to the first window and in the same execution release the application lock and run the test.