Set statistics I/O for nested loops

Consider the below query:

CREATE PROC dbo.GetPage  @orderid  AS INT    = 0, -- anchor sort key
            @pagesize AS BIGINT = 25
 AS
SELECT
TOP (@pagesize) orderid, orderdate, custid, empid
 FROM dbo.Orders WHERE orderid > @orderid ORDER BY orderid;

exec GetPage 25,25

SET STATISTICS IO for the above query returned:

(25 row(s) affected)
Table 'Orders'. Scan count 1, logical reads 87, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Explanation from Itzik Ben-Gan in his book goes like this for the above reads:


The I/O costs involved in the execution of the query plan are made of the following:

• Seek to the leaf of index: 3 reads (the index has three levels).

• Range scan of 25 rows: 0–1 reads (hundreds of rows fit in a page).

• Nested Loops prefetch used to optimize lookups: 9 reads (measured by disabling prefetch with trace flag 8744)

• 25 key lookups: 75 reads

Query plan

Now my question is, since nested loops does a key lookup once for each row returned from seek, should seek reads be 25*3 :75, the same as key lookups?

Query plan XML

`

Now my question is ,since nested loops does a key lookup once for each row returned from seek,should seek reads be 25*3 :75 same as key lookups

If the question is "should the seek also require 75 reads?" then the answer is no, for the reasons Itzik gave, and quoted in the question:



Seek to the leaf of index: 3 reads (the index has three levels)
Range scan of 25 rows: 0–1 reads (hundreds of rows fit in a page)


The initial seek to find the starting position of the range scan (in the Index Seek operator) takes 3 reads. From that point on, the storage engine remembers the current position of the scan, so fetching the next Index Seek row requires zero or one read. Zero reads if the next row is on the same page; one read if it is on the next page.

The difference in behaviour is a common source of confusion, and one of the reasons I dislike logical reads as a performance metric.

StackExchange Database Administrators Q#134172, answer score: 9