3

I have a query app scanning a whole table with a text field.

The query is doing this many reads:

Scan count 1, logical reads 170586, physical reads 3, read-ahead reads 174716, lob logical reads 7902578, lob physical reads 8743, lob read-ahead reads 0.

Query plan with lob logical reads

If I remove the text field from the select, reads become the following:

Scan count 1, logical reads 170588, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Query plan without lob logical reads

The thing that I don't get is how lob reads works:

if I sum up the logical reads with lob logical reads I get a total of 8.073.164 logical reads, which, if I'm correct, is about 64GB.

But the entire database is only 7GB!

I'm probably missing something about adding up logical reads and lob logical reads.

What does the number of lob logical reads actually represent?

Improve this question
5
  • 1
    I believe the execution plan for both situations are gonna be needed to understand the difference. It could be that your 1st query is reading the clustered index and the 2nd another index which does not contain that text field. About that large amount of logical reads, it could be a badly written query making use of RBAR and causing the table to be read way too many times to complete.
    – Ronaldo
    Aug 10, 2022 at 11:30
  • Hi Ronaldo! No differences at all: just a plain and simple select statement on a single table with no join and no where clause. The only difference is the list of columns in the select. The execution plan for both queries is just a clustered inxed scan. I'll provide both plans inside the question anyway
    – Mattia Nocerino
    Aug 10, 2022 at 12:04
  • Even for a simple SELECT with just a clustered index scan is still worth sharing the actual execution plan for which contains a lot of runtime information encoded in it besides just the operators. But I agree with Tibor that your query is probably resulting in the same data pages to be read multiple times, compounding the amount of data being read off disk.
    – J.D.
    Aug 10, 2022 at 13:12
  • 1
    @J.D. Query plans added!
    – Mattia Nocerino
    Aug 10, 2022 at 13:28
  • 2
    ActualLobLogicalReads is 0 in both of those plans
    – Charlieface
    Aug 12, 2022 at 12:17

2 Answers 2

Reset to default
5

Here's my theory,

These are logical reads, not physical reads. Imagine two rows, where the LOB data for these rows is on the same page (yes, pages can be shared across rows for LOB data).

Perhaps each read only reads, say, 100 bytes. Each read is still a logical read.

Furthermore, you say that these are text data type, for which the default is to not have the LOB data in-row. If the type instead were varbinary(max), then the default is to have LOB data in-row (as long as it fits on the page). This can make a big difference, what is best depends on whether you frequently need the LOB data or not. You can reconfigure this (for both types) using sp_tableoption.

Improve this answer
11
  • I'm sorry but I don't think I understand your answer: you're telling that 1 lob logical reads is equal to 1 byte?
    – Mattia Nocerino
    Aug 11, 2022 at 8:54
  • No that it now what I'm saying. Two rows can point to the same LOB page. Say that your SELECT read those two rows (only for sake of example), you have now two logical reads to that same page. Say that each row only has 100 bytes LOB data, you now did two logical read that read 200 bytes. Your calculation assumes that each logical read is 8 KB, which my example show that it doesn't have to me.
    – Tibor Karaszi
    Aug 11, 2022 at 12:12
  • Ok, so, while every logical read is 8KB, a lob logical reads can be anything, it depends on the content of the column. Do you have any official documentation that says that? Cause I don't think that's the case
    – Mattia Nocerino
    Aug 11, 2022 at 12:47
  • I think we might be talking about different things here, or misunderstand each other. You ask why can you have LOB logical reads that sums up to more than the database size. The answer is that the query can read the same LOB page over and over again (first read might be physical, subsequent reads might be only logical since the page is now possibly in cache). For each row you get a logical read to fetch the data, potentially several rows reads the same page (over and over again).
    – Tibor Karaszi
    Aug 11, 2022 at 13:31
  • @MattiaNocerino Two different rows of data in your table might be stored in the same 8 KB page of data. If your query returns both those rows, then the same 8 KB page of data is read from twice. So yes, it's 2 LOB logical reads, but from the same 8 KB page of data. Think of it like a book, and each page in the book is the equivalent of a data page in the database. Two different sentences may be on the same page. You open the book, turn to page 42 and read the first sentence. You jump to another page to read some other sentence. You jump back to the page 42 to read the second sentence.
    – J.D.
    Aug 14, 2022 at 0:52
0

I think I finally get it now. SET STATISTICS IO ON shows 8.073.164 lob logical reads, which is exactly the total number of records * 2.

That's because LOB data is stored in a B-Tree structure like normal pages! So, for every row, you perform 2 additional logical reads: one for the root page, and the other for the data itself.

That is in my specific case, because the largest text inside the table is only 24 bytes.

Source

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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