Logical and Physical read(s) - How it's working and How to manage ?

PandianS
Posted by in Sql Server category on for Advance level | Points: 250 | Views : 13052 red flag
Rating: 3.83 out of 5  
 6 vote(s)

We have heard about "Buffer Pool or Buffer Cache" - all the data pages are fetched from Buffer Cache or Buffer Pool... Correct ? How ?

Introduction

  • Normally, When we request a data for a query(i.e: SELECT), The Database engine fetches the data page(s) from the physical disk and loads into the Buffer Cache called "Physical Read".
  • "Physical Reads" occurrs only when data pages are not there in "Buffer cache".
  • Then , The data page(s) are retrived from the Buffer cache called "Logical Read"
  • If the data pages already there in Buffer Cache then, "Logical Reads" will be performed directly, no Physical read will be there. 
  • So, Disk IO reduced by using the "Logical Read", Because, The data pages retrived from Buffer Cache, not from Physical Disk .

How to test it ?

See the scenario given below...

/*1. Creating a Sample Table*/
Use Master

Go

Create Table Tb_Sales
(
Id Int Identity(1,1),
Column1 nVarchar (1000),
Column2 nVarchar (1000),
Column3 nVarchar (1000)
)
Go

/*Is there any data page(s) in "Buffer Cache" ?*/

Use Master

Go

Select 
[Database], 
[Object], 
page_type [Cached Page Type], 
cached_pages [Cached Page(s)],
(cached_pages * 8) [Cached Page(KB)],
(cached_pages * 8)/1024. [Cached Page(MB)],
((cached_pages * 8)/1024.)/1024. [Cached Page(GB)]
From
(
SELECT bd.page_type, COUNT(1) AS cached_pages,
CASE bd.database_id WHEN 32767 THEN 'ResourceDb'
ELSE DB_NAME(bd.database_id)
END AS [database], Object_Name(p.[object_id]) [Object]  
FROM sys.dm_os_buffer_descriptors bd join sys.allocation_units au
on (bd.allocation_unit_id = au.allocation_unit_id)
JOIN sys.partitions P ON ((p.partition_id  = au.container_id and au.[type] =3) or (p.hobt_id  = au.container_id and au.[type] in(1,2)))
where  p.[object_id] = object_id('Tb_Sales')
GROUP BY DB_NAME(bd.database_id), bd.database_id, bd.page_type ,Object_Name(p.[object_id])
)as [SQL]


Nothing will be there in Buffer cache. Because, we don't have any data in table correct ?

/*Inserting sample data*/
Use Master

Go

Insert Tb_Sales (Column1,Column2, Column3) Values(REPLICATE('a',1000),REPLICATE('b',1000),REPLICATE('c',1000))
go 1000

/*Is there any data page(s) in "Buffer Cache" Now ?*/
Use Master

Go

Select 
[Database], 
[Object], 
page_type [Cached Page Type], 
cached_pages [Cached Page(s)],
(cached_pages * 8) [Cached Page(KB)],
(cached_pages * 8)/1024. [Cached Page(MB)],
((cached_pages * 8)/1024.)/1024. [Cached Page(GB)]
From
(
SELECT bd.page_type, COUNT(1) AS cached_pages,
CASE bd.database_id WHEN 32767 THEN 'ResourceDb'
ELSE DB_NAME(bd.database_id)
END AS [database], Object_Name(p.[object_id]) [Object]  
FROM sys.dm_os_buffer_descriptors bd join sys.allocation_units au
on (bd.allocation_unit_id = au.allocation_unit_id)
JOIN sys.partitions P ON ((p.partition_id  = au.container_id and au.[type] =3) or (p.hobt_id  = au.container_id and au.[type] in(1,2)))
where  p.[object_id] = object_id('Tb_Sales')
GROUP BY DB_NAME(bd.database_id), bd.database_id, bd.page_type ,Object_Name(p.[object_id])
)as [SQL]



/*Now, The data pages will be there "Buffer Cache"*/

/*Performing SELECT statement with IO, Time*/

Use Master

Go

Set Statistics IO,Time On
Go
select * from Tb_Sales
Go
Set Statistics IO,Time Off


/*Result for "IO" and "Time" for the SELECT statement - The following result will be there in "Messages" Tab*/

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1000 row(s) affected)
Table 'Tb_Sales'. Scan count 1, logical reads 1000, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 358 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.[/CODE]- The table "Tb_Sales" have 1000 records.


  • All the data fetched from Buffer Cache (Logical Reads : 1000), Not from the physical disk (Physical reads : 0 or read-ahead reads : 0)
  • Total Time taken from this query completion : 358 Milliseconds ( 0.358 Seconds )

Let''s Remove all the data pages from the "Buffer Cache".

So, what will happen ?

Next time, when we try to fetch the data from the table, All the data pages will be fetched from "Physical Disk" and copied into "Buffer Cache",But,  Not directly from the "Buffer Cache" correct ?, Because, Just now we have removed all the data pages from the "Buffer Cache"... :)

Note: Always, "Logical Reads" will be performed. But, The conclution is whether the "Physical Reads" additionally performed or not... :)

/*To remove all the Data from the Buffer Cache(Buffer Pool)*/
Use Master

Go

DBCC DropCleanBuffers
Go


CAUTIONThis statement removes all the data from the Buffer Pool, Not only for the particular table. This statemnent specific to the Database, Not for a particular Table.

/*Performing SELECT statement with IO, Time again*/

Use Master

Go

Set Statistics IO,Time On
Go
select * from Tb_Sales
Go
Set Statistics IO,Time Off


/*Result for "IO" and "Time" for the SELECT statement - The following result will be there in "Messages" Tab*/

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1000 row(s) affected)
Table 'Tb_Sales'. Scan count 1, logical reads 1000, physical reads 1, read-ahead reads 995, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 642 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

  

  • The table "Tb_Sales" have 1000 records.
  • All the data fetched from Buffer Cache (Logical Reads : 1000).
  • But, 1 Page from Physical disk "Physical reads : 1",  995 Pages from "Read-ahead reads : 995"
  • Total Time taken from this query completion : 642 Milliseconds ( 0.642 Seconds )

Conclusion

  • All the data pages will be read from the "Physical Disk", If the data not there in "Buffer Cache".
  • "Logical Reads" will be performed every time.
  • "Physical Reads" will be performed Only when the data is not in "Buffer Cache". NOT ALWAYS.
  • Additionally, By the Database Engine performes something called "Read-ahead Reads" to retrive data and index pages needed to fulfill a query execution plan and retrives the pages into the "Buffer Cache or Buffer Pool" before they are actually used by the query.
Page copy protected against web site content infringement by Copyscape

About the Author

PandianS
Full Name: Pandian Sathappan
Member Level: Silver
Member Status: Member,MVP
Member Since: 5/11/2009 2:24:28 AM
Country: India
Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions
http://sqlserverbuddy.blogspot.com/
Microsoft Certification - MCITP: Microsoft Certified IT Professional - SQL Server 2008 Database Administrator - MCTS: Microsoft Certified Technology Specialist - SQL Server 2005 - MCP: Microsoft Certified Professional - SQL Server 2000

Login to vote for this post.

Comments or Responses

Posted by: SheoNarayan on: 5/2/2011 | Points: 25
Very good PandianS.

Keep it up!
Posted by: Chvrsri on: 5/3/2011 | Points: 25
Hi PandianS,

Nice Article !!!
Posted by: PandianS on: 5/3/2011 | Points: 25
Thanks to all :)
Posted by: Susanthampy on: 5/18/2011 | Points: 25
Nice article......................
Posted by: srilakshmivaranasi123-10757 on: 6/23/2011 | Points: 25
artical is nice..............
Posted by: PandianS on: 6/23/2011 | Points: 25
Thanks!

Login to post response

Comment using Facebook(Author doesn't get notification)