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
santoooo
santoooo
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 253
Hi,

I heard abt the Logical/ Physical Reads In Sql Server.

Could any one please explain me

What are Logical Reads?
What are Physical Reads?

and also heared, Less no of Logical Reads improves the Query performance.

please explain me how can we reduce the logical reads..?

Thanks,
Santosh
David Benoit
David Benoit
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3386 Visits: 3650
From BOL

Logical reads - number of pages read from the data cache
Physical reads - number of pages read from disk

To reduce reads you need to look at a couple of things, first being query design, secondly being indexing. If your query is pulling a large number of records that could be filtered by getting a smaller set prior to pulling that data then you can always cut down on reads that way. With improved indexing, specifically with covering indexes, you can reduce the number of pages that are being read as well.

All of this is just a basic guideline but should be applied to the analysis of all the queries that are running in your production environment. A query like the following (not mine but got this from somewhere else so, no credit here) should help in getting those queries.

SELECT TOP 20
SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((
CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1) AS SQLText
, qs.execution_count
, qs.total_logical_reads
, qs.last_logical_reads
, qs.min_logical_reads
, qs.max_logical_reads
, qs.total_elapsed_time
, qs.last_elapsed_time
, qs.min_elapsed_time
, qs.max_elapsed_time
, qs.last_execution_time
, qp.query_plan
FROM
sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE
qt.encrypted=0
ORDER BY
qs.last_logical_reads DESC

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
santoooo
santoooo
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 253
Hi,

Thank you for your valuable information.

So, to reduce the Logical Reads we need proper indexing for the tables.


Regards,
Santosh
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10420 Visits: 11961
santoooo (9/17/2008)

So, to reduce the Logical Reads we need proper indexing for the tables.


Regards,
Santosh

No, you need proper database.
Proper schema, proper data normalisation, proper data types, proper keys, proper indexes, etc.

You need to open a good book about relational DB design and follw its recommendations.
papapumpy
papapumpy
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 1
Yes, indexing helps but reducing logical reads does not help performance. maybe a tiny bit

read some article about buffer hit ratio and data cache. physical reads is the one takes significantly longer time. i actually wrote my final term paper about that when i was in CS in my college long time ago.

Dr. Inner Join
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: 87673 Visits: 45272
You did notice that this thread is over a year old?

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


papapumpy
papapumpy
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 1
LOL!
John Rowan
John Rowan
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6064 Visits: 4551
papapumpy (1/5/2010)
Yes, indexing helps but reducing logical reads does not help performance. maybe a tiny bit

read some article about buffer hit ratio and data cache. physical reads is the one takes significantly longer time. i actually wrote my final term paper about that when i was in CS in my college long time ago.

Dr. Inner Join



The thread may be over a year old, but I have to add a correction as to not confuse new readers. Reducing logical reads [b]does[/do] improve query performance! In fact, logical reads is one of the best metric to look at when optimizing a query. Logical reads represents the data that must be read from cache in order to process a query. The less amount of data needed to process a query, the better it performs.

Using SET STATISTICS IO ON when optimizing queries can help you quickly, by looking at the logical reads counter, determine where to focus your tuning efforts. Seeing the logical reads by object can quickly point you to poorly performing sections of your query.

So to recap, I think the statement that "reducing logical reads does not help performance. maybe a tiny bit" is not true.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
santoooo
santoooo
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 253
Dear All,

Thank you very much for your information.
Dinesh Babu Verma
Dinesh Babu Verma
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 110
Summary Info:

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



Details 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

Thanks,
Dinesh Babu Verma
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