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 |
