Logical and Physical Read in SQL Server Performance
By : Kasim Wirama, MCDBA
Every query has its execution plan before it gets executed. Execution plan is generated by optimizer, one of components in SQL Server database engine; it contains how to access data to satisfy the query request. You probably meet few queries having slow response time by looking into physical read and logical read. How do you get information about logical and physical for a query? I will tell you how and what it means to your query.
You open a connection in SSMS (SQL Server Management Studio) or Query Analyzer, then execute SET STATISTICS IO ON. After that you can execute query that is slow. And you will get information logical and physical read on a related table(s).
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 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 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