LOB (Large object) Logical reads refers to the number of pages reads from the buffer pool. LOB may be TEXT, NTEXT, IMAGE, XML, VARBINARY, VARCHAR (MAX), NVARCHAR (MAX) and FILESTREAM. Logical reads refers to pages of regular data like int , varchar, nvarchar, bigint , datetime etc while LOB is refer to read the LOB pages from Buffer pool.
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.
|
select * from LOB_Read_Test |
Logical read happen for the two column (LOB_Read_Test_ID
& Col_varchar) and LOB Logical read happen for two the LOB columns.
