Skip to main content
Clarification for Erik, Darling
Source Link
Paul White
  • 95.8k
  • 30
  • 442
  • 691

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.

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 takes 3 reads. From that point on, the storage engine remembers the current position of the scan, so fetching the next 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.

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.

Correct typo.
Source Link
Dan Guzman
  • 29.1k
  • 2
  • 47
  • 71

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 takes 3 reads. From that point on, the storage engine remembers the current position of the scan, so fetching the next 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 readsreasons I dislike logical reads as a performance metric.

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 takes 3 reads. From that point on, the storage engine remembers the current position of the scan, so fetching the next 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 reads I dislike logical reads as a performance metric.

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 takes 3 reads. From that point on, the storage engine remembers the current position of the scan, so fetching the next 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.

Source Link
Paul White
  • 95.8k
  • 30
  • 442
  • 691

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 takes 3 reads. From that point on, the storage engine remembers the current position of the scan, so fetching the next 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 reads I dislike logical reads as a performance metric.