Lob Physical read in SQL server

LOB (Large object) Logical reads refers to the number of pages reads from the Disk because these pages are not in buffer pool. LOB may be TEXT, NTEXT, IMAGE, XML, VARBINARY, VARCHAR (MAX), NVARCHAR (MAX) and FILESTREAM. Physical reads refers to pages of regular data like int , varchar, nvarchar, bigint , datetime etc while LOB is refer to read the LOB pages from Disk.

LOB data types are generally store across the multiple pages in the database due to that we are reading the LOB column it required more I/O Operation. To avoid selecting LOB data unnecessarily in our query. By reducing the LOB reads we can improve the overall performance of our query.

See the example

Creating a new table which have LOB columns.

create table LOB_Read_Test ( LOB_Read_Test_ID int not null identity(1,1) PRIMARY Key, Col_varchar varchar(128) not null, Col_varchar_Max varchar(max) null, Col_varbinary varbinary(max) null );

Table created successfully. 

Now Inserting 10k records in this table.

declare @i int =1; while @i<=10000 begin insert into LOB_Read_Test(Col_varchar,Col_varchar_Max,Col_varbinary) select 'Col_varchar ' + cast(@i as varchar(10)), replicate('a',12000), convert(varbinary(max),replicate(convert(varchar(max),'b'),12000)) set @i=@i+1; end

10K records inserted successfully.

Now Reading the data from this table.

SET STATISTICS  IO ON select * from LOB_Read_Test