When it comes to SQL Server query tuning, two of the most used phrases are logical reads and physical reads. But do you know exactly what are these, how they distinguish? It is not a very complicated thing, but recently I started explaining small things for me (and you) so when we go to complex stuff we have a strong base to sit on.
Logical Read
As a measurement, we are told always use logical reads. Logical read is a page read from the buffer pool (SQL Server's in-memory cache). Every page comes to SQL server, through buffer pool. So, it is good and stable measurement to see how much data, query is playing with.
Physical read is a page that had to be pulled from disk, because it is not in the buffer pool (yet). Therefore, physical reads are always subset of logical reads. When page is read from disk it is placed in buffer pool. Then SQL server read from there.
Since data is coming from disk, more physical reads mean, more dealy.
Let's have a look into an example.
SET STATISTICS IO ON;
SELECT * FROM Sales.SalesOrderDetail
WHERE ProductID = 776;
In my computer, output was:
(228 rows affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 1128, physical reads 3,
page server reads 0, read-ahead reads 120, page server read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob page server reads 0,
lob read-ahead reads 0, lob page server read-ahead reads 0.
If I run second time physical reads will be zero.
(228 rows affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 1128, physical reads 0,
page server reads 0, read-ahead reads 0, page server read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob page server reads 0,
lob read-ahead reads 0, lob page server read-ahead reads 0.
In both occations, logical reads were same, but physical reads were 3 in first run and 0 in second run (because pages were in buffere pool in second run).
Hope that clarifies.
P.S. If you wondering about "Read-ahed reads", they are also physical reads, but it is just SQL Server being proactive. When SQL server fetch some pages from disk, if it sense, user will query data relatively close to what it fetch, it reads those pages as well and put them in buffere pool. But those read ahead pages were not used in the query that just ran.
