Logical Read, Physical Read and Buffer Cash hit

Logical Reads: 

This is also known as cache hit, which means reading pages from cache memory instead of disk. Logical reads in 

Logical read specifies total number of data pages needed to be accessed from data cache to process query. It is very possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query.

Physical Reads 
Physical read indicates total number of data pages that are read from disk. In case no data in data cache, the physical read will be equal to number of logical read. And usually it happens for first query request. And for subsequent same query request the number will be substantially decreased because the data pages have been in data cache.

Buffer Cash Hit Ratio: 

The more logical read count, better would be cash hit ratio.
(logical reads – physical reads)/logical read * 100%. The high buffer hit ratio (if possible to near 100%) indicates good database performance.

Some warning on high number of logical read:

Higher number of Logical Reads tends high memory usage, but there are various way by which we can reduce higher number of logical read

1. Remove Improper/Useless/Insufficient Indexes: Indexes should be build on the basis of data access or retrieval process if any of the indexes is build on the columns which are not used in a query will leads to High Logical reads and will degrade the performance while reads and writing the data....
2.  Poor Fill Factor/Page Density: Page use should not be very less. otherwise large number of page will be used for small amount of data which will also leads to High Logical Reads....
3. Wide Indexes: Indexing on the large number of columns will leads to high logical reads....
4. Index scanning: if query is leads to index scanning on the table then logical reads will be high...

Logical Reads count can be get by using following ways

1.     set statistics io on 
2.          sys.dm_exec_query_Stats
3.          SQL Profiler: by executing the sql profiler on that database we can find out logical reads..

Example

set statistics io on

set statistics time on

    select * from dbo.person

    select * from dbo.person

set statistics io off

set statistics time off

go

(19972 row(s) affected)

Table 'Person'. Scan count 1, logical reads 150, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:

   CPU time = 15 ms,  elapsed time = 963 ms.

(19972 row(s) affected)

Table 'Person'. Scan count 1, logical reads 150, 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 = 564 ms.

 SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 0 ms.

Enjy coding…SQL J

Post Reference: Vikram Aristocratic Elfin Share