1
dbcc FREEPROCCACHE
dbcc DROPCLEANBUFFERS

set STATISTICS IO ON--Case 1
SELECT * from Production.Suppliers s


--(30 row(s) affected)
--Table 'Suppliers'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--again without clearing the cache I ran above

set STATISTICS IO ON
SELECT * from Production.Suppliers s


--(30 row(s) affected)
--Table 'Suppliers'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I don't understand when I clear cache in case 1 how it shown logical reads 3, what i read is logical read means no. of pages read from data cache but I'm clearning in CASE 1 before executing the sql statement still it's giving me logical reads 3 when data cache is cleared

why?

Registered User
  • 1,554
  • 3
  • 22
  • 37

2 Answers2

2

Note the "physical reads" difference

Simply,

  • physical reads = data into cache
  • logical reads = accesses to data in cache

Clearing the cache forces a physical read so you can have logical reads. You can not have zero logical reads (for the pedants, except where a logical read means rows but that is out of scope here)

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
-1

A logical read does not mean the page was in cache before the query started. Every page that SQL Server has to access gets counted as a logical read. if the page has to be access two times, two logical reads get counted. Every time a page has to be accessed, SQL Server has to execute code to prevent changes to that page while it is reading it (It is using latches for that). So a logical read is a fairly expensive operation. The number of logical reads is therefore a good indicator of how expensive a query is.

Sebastian Meine
  • 11,260
  • 29
  • 41