Logical Read Vs Physical Read along with Buffer Hit Ratio.
Logical Reads: Reading
data pages from Cache.
Physical Reads:
Reading Data Pages from Hard Disk
Buffer Cache Hit
Ratio: (logical reads-Physical Reads)/logical read*100%
Logical Reads: Logical read indicates total number of
data pages needed to be accessed from data cache to process a query. It is very
possible that logical read will access the 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 the total number of 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 the data cache.
Buffer Cache Hit
Ratio: Buffer hit ratio will be
calculated based on these two kinds of read as the following formula:
(Logical Reads-Physical
Reads)/logical read*100%
The High Buffer hit ratio
(if possible near to 100%) indicates good database performance on SQL Server level.
So use information from Physical read and Buffer hit ratio to measure
performance in server level and logical read to measure individual Query level.
Excess of the logical reads tends high memory usage. There
are some ways by which we can reduce logical reads
1) Improper/Useless/Insufficient Indexes: Indexes should be built on the basis of
data access or retrieval process if any of the indexes is built on the columns
which are not used in a query will lead 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 columns will
lead to High logical Reads
4) Index Scanning: If a Query is leads to index scanning on the
table then logical reads will be high.
How to get the Logical Read Count?
Below are the
ways to check logical Reads
SET STATISTICS
IO ON
2) SYS.DM_EXEC_QUERY_STATS: By executing the below statements we can find
detailed info about read and write
SELECT * FROM sys.dm_exec_query_stats
3) SQL Profiler: By executing the sql profiler on that
database we can find out logical reads
There are also some other DMV’s which will aslo
help us to get logical reads
Comments