Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Logical/Physical Reads!!!! Expand / Collapse
Author
Message
Posted Friday, February 10, 2012 3:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 1:37 AM
Points: 6, Visits: 83

Summary Info:

Logical Reads : Reading Data pages from Cache
Physical Reads : Reading Data pages from Hard Disk
Buffer Cach Hit Ratio logical reads – physical reads)/logical read * 100%



Detailed Info:

Logical Reads:
Logical read indicates 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
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 to near 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


Execess 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 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 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 follwoing ways

Below are the ways to check logical Reads:
1. set statistics io on

2. sys.dm_exec_query_Stats
by executing the below statement we can find detailed info about reads/writes
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 some other SQL DMV/F which also help us to get logical reads...


Thanks,
Dinesh Babu Verma


Post #1250154
Posted Friday, February 10, 2012 4:22 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 4,914, Visits: 8,652
And the question is?

--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1250174
Posted Friday, February 10, 2012 4:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:47 AM
Points: 41,525, Visits: 34,442
Is there a question here?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1250175
Posted Friday, February 10, 2012 5:31 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 12:58 AM
Points: 1,904, Visits: 2,771
GilaMonster (2/10/2012)
Is there a question here?


maybe it was suppose to be an article submission?


----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #1250212
Posted Wednesday, February 19, 2014 2:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 9:14 PM
Points: 1, Visits: 110
Hi,

I was recently asked in an interview about the topic & had I idea about the same but in absence of proper knowledge I was not able to answer properly. After reading this article I got a very nice idea about the same for thank u very much for posting a very good post.

Regards
Post #1542900
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse