InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 11:20:33 GMT20http://www.sqlservercentral.com/Forums/Topic571160-8-1.aspxNice Explaination..Thanks.Sun, 27 Jan 2013 10:40:39 GMTmauleshmevadahttp://www.sqlservercentral.com/Forums/Topic571160-8-1.aspx[quote][b]Dinesh Babu Verma (2/8/2012)[/b][hr]In case no data in data cache, the physical read will be equal to number of logical read. [/quote]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.[quote][b]Buffer Cash Hit Ratio[/b] 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 [/quote]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.[url]http://www.simple-talk.com/sql/database-administration/great-sql-server-debates-buffer-cache-hit-ratio/[/url]p.s. Over 3 year old thread.Wed, 08 Feb 2012 03:41:55 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic571160-8-1.aspxSummary Info:Logical Reads : Reading Data pages from CachePhysical Reads : Reading Data pages from Hard DiskBuffer Cach Hit Ratio :(logical reads – physical reads)/logical read * 100%Details Info:[b]Logical Reads:[/b]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.[b]Physical Reads [/b]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.[b]Buffer Cash Hit Ratio[/b] 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 VermaWed, 08 Feb 2012 02:15:18 GMTDinesh Babu Vermahttp://www.sqlservercentral.com/Forums/Topic571160-8-1.aspxDear All,Thank you very much for your information.Wed, 06 Jan 2010 06:21:18 GMTsantoooohttp://www.sqlservercentral.com/Forums/Topic571160-8-1.aspx[quote][b]papapumpy (1/5/2010)[/b][hr]Yes, indexing helps but reducing logical reads does not help performance. maybe a tiny bitread 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[/quote]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.Tue, 05 Jan 2010 15:37:33 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic571160-8-1.aspxLOL!Tue, 05 Jan 2010 15:21:23 GMTpapapumpyhttp://www.sqlservercentral.com/Forums/Topic571160-8-1.aspxYou did notice that this thread is over a year old?Tue, 05 Jan 2010 15:11:56 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic571160-8-1.aspxYes, indexing helps but reducing logical reads does not help performance. maybe a tiny bitread 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 JoinTue, 05 Jan 2010 15:04:47 GMTpapapumpyhttp://www.sqlservercentral.com/Forums/Topic571160-8-1.aspx[quote][b]santoooo (9/17/2008)[/b][hr]So, to reduce the Logical Reads we need proper indexing for the tables.Regards,Santosh[/quote]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.Wed, 17 Sep 2008 22:53:13 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic571160-8-1.aspxHi,Thank you for your valuable information.So, to reduce the Logical Reads we need proper indexing for the tables.Regards,SantoshWed, 17 Sep 2008 22:16:56 GMTsantoooohttp://www.sqlservercentral.com/Forums/Topic571160-8-1.aspxFrom BOLLogical reads - number of pages read from the data cachePhysical reads - number of pages read from diskTo 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 20SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offsetWHEN -1 THEN DATALENGTH(qt.text)ELSE qs.statement_end_offsetEND - 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_planFROMsys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qtCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpWHEREqt.encrypted=0ORDER BYqs.last_logical_reads DESCWed, 17 Sep 2008 10:47:34 GMTDavid Benoithttp://www.sqlservercentral.com/Forums/Topic571160-8-1.aspxHi,I heard abt the Logical/ Physical Reads In Sql Server.Could any one please explain meWhat 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,SantoshWed, 17 Sep 2008 10:29:35 GMTsantoooo