SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Reduce the Logical Reads, to imporve the Performance of the Query


How to Reduce the Logical Reads, to imporve the Performance of the Query

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87735 Visits: 45272
Dinesh Babu Verma (2/8/2012)

In case no data in data cache, the physical read will be equal to number of logical read.


Not necessarily, because a query could request the same page more than once. If the page doesn't start in cache, the first will be a physical read, the others will not.

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


Buffer cache hit ratio is a near-useless counter. By the time it drops significantly the server would have been having severe problems for a while.
http://www.simple-talk.com/sql/database-administration/great-sql-server-debates-buffer-cache-hit-ratio/

p.s. Over 3 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


mauleshmevada
mauleshmevada
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 67
Nice Explaination..

Thanks.
ferrari_martin
ferrari_martin
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 0
Really old thread now, but just to add a little bit to John's answer, logical reads should be the first thing to try to tune. It *might* not have a big impact on some queries on low traffic servers, compared to physical reads which are more costly, but as queries get more complex, and the load and concurrency starts to grow, logical reads start to result in more resources needed to process queries. That's because clearly the more data you need to process, the more time it will take, and if you start adding up many queries being executed at the same time, the load on the server can grow considerably. The data will also take more space, which should be less of a problem nowadays, with memory being so cheap.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search