Hi,

I was doing some tests with LOB fields. I created one table with 200 records, but one simple select on the table results in 666 LOB logical reads.

So, how are the LOB logical reads happening that results in a value more than 3 times the number of records ?

The records are small, the table has only 57 pages, with 2 IAM's and one in-row data page, there are 54 LOB data pages.

How this 666 reads happend ?

Here is my script:

create table LOB1
(id int identity(1,1) not null primary key,
  name varchar(50),
  lfield text
  )
go


insert into LOB1 values('John',replicate('x',900))
go 100
insert into LOB1 values('John',replicate('x',2000))
go 100

Thank you !

Hi,

This is exactly what happens with LOB pages. The part of data is stored on In row page type and other part is stored on off row. So when you do select * it would read in row pafe as well as LOB pages thus increasing count to twice on single read.

In your case it has to read two data pages and then the LOB page. Just used below query to check page allocation (in SQL Server 2012 and above, the command is undocumented)

SELECT
    rowset_id,
    allocation_unit_id,
    allocation_unit_type_desc,
    allocated_page_page_id,
    is_mixed_page_allocation,
    page_free_space_percent,
    page_type,
    page_type_desc
FROM sys.dm_db_database_page_allocations(DB_ID(N'master'), OBJECT_ID(N'LOB1', N'U'), NUll, Null, 'DETAILED') 

result is

You can see from screen two data pages and many text_mix pages. LOB data is stored in different page format from normal data and so since you have 2 data pages and many text_mix pages every read is reading both data pages and then the text_mix page to get the data so you have thrice reads which is 666

Hi, Vitaly,

Thank you.

I already did this query. I have 1 data page, not two, and one IAM for the data page.

If every record is reading the data page and the LOB page the result would be 400. 

It's important to notice that I'm calculating only the LOB logical reads. When the query doesn't include the LOB field, it has 3 logical reads (I assume it's two IAM's and the data page). When it include the LOB field the 666 LOB logical reads are added.

If SQL Server have already read the data page to retrieve in-row data, why it read the same page again for the same record ?

To achieve 600 as results we should also add the IAM to the count, so SQL Server is reading the IAM two times for each record ? Why this count is calculated like this for LOB records, but without the LOB field the logical read count is only 3, not 200 ?

Even adding the LOB, there is still more 66 logical reads.

Thank you !

I already did this query. I have 1 data page, not two, and one IAM for the data page.

Than can vary as per data present in table

If every record is reading the data page and the LOB page the result would be 400. 

It's important to notice that I'm calculating only the LOB logical reads. When the query doesn't include the LOB field, it has 3 logical reads (I assume it's two IAM's and the data page). When it include the LOB field the 666 LOB logical reads are added.

Yes if you have one IAM file and one In row data then reads would be 400 because reads would be like one IAM page one Data page and One LOB data page. The read would take help of IAM page to locate data page and then using data page it would go to LOB data page

I am not sure where 66 extra reads come from may be something SQL Server is doing internally.

When you store LOB data in SQL Server, it is stored in LOB_DATA and in a page type of its own designated as Text/Image.  The page is store along with the IN_ROW_DATA page in a sense utilizing a 16 byte pointer that is used to refer to the LOB_DATA in the page.

First, don't use text. Use varchar(max).  For varchar(max) the default is to store the value directly on the row if it is small.  You can force it off-row with a table option.

With varchar(max) it would look like this:

--drop table LOB1
set nocount on 

create table LOB1
(id int identity(1,1) not null primary key,
  name varchar(50),
  lfield varchar(max)
  )
go
exec sp_tableoption 'LOB1', 'large value types out of row', 1
go
insert into LOB1 values('John',replicate('x',900))
go 100
insert into LOB1 values('John',replicate('x',2000))
go 100

go
set statistics io on 
go
select * from lob1
go
set statistics io off
go

The reasons you see multiple LOB logical reads per row is a because when stored off-row, LOBs are stored in a BTree.  So the pointer on the data page points to the root of a BTree, which contains the page pointers for the actual data.  So there's at least 2 Logical IOs required to actually get the LOB data.

See

Microsoft SQL Server 2012 Internals pps 386-389

by Kalen Delany

She also explains there how this differs slightly with the old text and image data types.

David

Hi, David,

"First, don't use text. Use varchar(max). "

I'm doing tests with both. First I would like to understand this 666 lob logical reads with text data type.

"The reasons you see multiple BLOB reads per row is a because when stored off-row, LOBs are stored in a BTree.  So the pointer on the data page points to the root of a BTree, which contains the page pointers for the actual data.  So there's at least 2 Logical IOs required to actually get the LOB data."

This happens when the LOB data is over 32kb. In my sample script (I posted it above) my lob data is small, so the b-tree isn't created. I checked this with sys.dm_db_page_allocations. The article http://blogs.lessthandot.com/index.php/datamgmt/dbadmin/lob-logical-reads-twice-the/ explains this and demonstrate the pages with type 3 and 4, I don't have two page types in LOB data pages, only one, exactly because the data is small.

Thank you !

Hi,

"Yes if you have one IAM file and one In row data then reads would be 400 because reads would be like one IAM page one Data page and One LOB data page. The read would take help of IAM page to locate data page and then using data page it would go to LOB data page"

Yes, but the result is 666.

If I add the fact that I have one IAM for the data pages and another IAM for the LOB data pages, so 3 x 200=600. But two questions remains:

1) Why, when the lob field is in the query, each IAM is read 200 times, while when the LOB field isn't in the query, it doesn't happen ? The total amount of logical reads when the lob field isn't in the query is 3.

2) There is still the difference of 66 LOB logical reads.

Thank you !

>This happens when the LOB data is over 32kb. In my sample script (I posted it above) my lob data is small, so the b-tree isn't created.

This is not correct.  The BTree is still created.  The BTree root is on a page that may also contain LOB data, but it's not eliminated.

If you add a row like this

insert into LOB1 values('Fred',replicate('y',900))

You can follow the page pointers by first dumping the data page, then the value of the LOB pointer, etc. For me it turned out to be

DBCC TRACEON (3604);
dbcc page(18,1,305,3) --the data page
dbcc page(18,1,324,3) --the LOB page pointed to
dbcc page(18,1,325,2) -- the LOB page containing the data
Page 324 had this record:

Blob row at: Page (1:324) Slot 17 Length: 84 Type: 5 (LARGE_ROOT_YUKON)

Blob Id: 137691136 Level: 0 MaxLinks: 5 CurLinks: 1

	Child 0 at Page (1:325) Slot 3 Size: 900 Offset: 900

Davi