I/O - Explained

The primary purpose of the databae is to store and retrieve data over and over. during the operation lot of disk READS and WRITES will be there.

So, Disk I/O operation takes many resources and take long time to complete the READ/WRITE operations.

In SQL Server, It uses Virtual Memory to a buffer cache and uses the cache to reduce the Physical I/O. Physical I/O is nothing but reading and writing data from actual data page every time insteadof cache.

Physical Read: Reading data from the actual data pages into Cache.

Logical Read: Reading data from the Cache insteadof actual data pages. Anyway, Every time logical read will be there (If it is Physical/Logical read).

If the requested page is not there in Cache then only the Physical read operation will happen.

To Identify that how many Pages allocated for a table:

USE AdventureWorks
GO

SELECT  
a.total_pages 'Total Pages'
FROM sys.allocation_units A JOIN sys.partitions P
ON (A.container_id = P.hobt_id)
JOIN sys.sysobjects O
On (O.id = p.[object_id])
WHERE O.[type] = 'U'
AND O.id = OBJECT_ID('Employee','U')
There 9 Page(s) allocated for the table: Employee

How to identify the I/O : (Physical, Logical, Read-Ahead Reads)

We can identify the I/O using SET STATISTICS IO statement.

The Data cache should be flushed before start, Then only we will have exact statistics.
Step:1
 
To Clear the Data  Cache:
DBCC DROPCLEANBUFFERS

To identify the I/O :
 
USE AdventureWorks
Go

SET STATISTICS IO ON

SELECT * FROM Employee
SET STATISTICS IO OFF

Result:

Here, Data cache is Blank, So the 3 physical reads performed on actual data page from the disk.


Step: 2(Without any data cache clear)
Executing the script

USE AdventureWorks
Go

SET STATISTICS IO ON
SELECT * FROM Employee
SET STATISTICS IO OFF

Result:

Now, Data already there in data cache, so, the logical read only performed. There is no Physical read from the actual data page on disk.