Database Administration
sql-server sql-server-2017 database-internals
Updated Sat, 17 Sep 2022 21:11:34 GMT

Logical reads and LOB logical reads


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?




Solution

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