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

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

CAUTION: This 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