Logical Reads vs. Physical Reads

If you’ve spent time tuning SQL Server queries, you may have seen these two numbers side by side: logical reads and physical reads. Maybe you have seen one or the other be some absurdly high number and didn’t think much of it. But in reality they could be eye openers on behind the scenes query performance.

The quick version

• Logical reads are pages a query can pull from the data cache — recently accessed pages stored in memory.
• Physical reads are pages that come from disk instead, which comes with a much greater I/O hit.

In a healthy, well-cached query, physical reads should be a small fraction of logical reads — something like 50,000 logical reads against 200 physical reads, with nearly everything served from memory.

Real World Example

I was investigating a specific long-running query, and pulling it up in Database Health Monitor’s Long Running Query report is where the numbers stood out: roughly 182 million physical reads against only about 13 million logical reads. Converted to actual data volume, that’s around 1.42TB pulled off disk against roughly 104GB of pages read from the data cache.

Because so much of this was being loaded into temp tables for later data manipulation, we were seeing a squeeze on both TempDB and shared memory for other processes. This cascaded into an overall slowdown on a pretty beefy server as this one query chugged along. Improving the queries that were being used to load the temp tables greatly reduced the overall stress this put on the server, as it reduced IO load from physical reads, and the TempDB space used by the temp tables.

What To Watch For

Don’t just look at physical reads in isolation and assume “big number, bad query.” Look at the relationship between physical and logical. When physical reads are a small fraction of logical reads, you’re looking at a query that’s mostly living in memory the way you want it to. When physical reads start closing in on — or exceeding — logical reads, that’s worth investigating. It usually means something (often exactly this: large temp table or worktable activity) is pushing more out of the buffer pool than the server can comfortably hold, and SQL Server is paying for it in disk I/O.

A query you can run yourself

Database Health Monitor is a great way to look at Long Running Queries on your server, but you can also run the query below to look at recent executions and compare Logical and Physical reads.

SELECT TOP (50)    DB_NAME(st.dbid) AS DatabaseName,    qs.execution_count AS Executions,    qs.total_logical_reads AS TotalLogicalReads,    qs.total_physical_reads AS TotalPhysicalReads,    CAST(qs.total_logical_reads * 8.0 / 1024 / 1024 AS DECIMAL(10,2)) AS LogicalReadsGB,    CAST(qs.total_physical_reads * 8.0 / 1024 / 1024 AS DECIMAL(10,2)) AS PhysicalReadsGB,    qs.last_execution_time AS LastRun,    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,        ((CASE qs.statement_end_offset            WHEN -1 THEN DATALENGTH(st.text)            ELSE qs.statement_end_offset          END - qs.statement_start_offset) / 2) + 1) AS QueryTextFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stORDER BY qs.last_execution_time DESC;