Post reply

Why are the logical reads appearing in IO STATISTICS even after cleaning Cache

  • December 18, 2022 at 10:39 am

    #4127091

    Hello,

    I am trying to understand the output of IO STATISTICS .

    I run some queries like as bellow :

    DBCC FREEPROCCACHE
    DBCC DROPCLEANBUFFERS

    SET STATISTICS TIME ON
    SET STATISTICS IO ON
    SELECT * FROM [HumanResources].[Employee]
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF

    The output is

    SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

    (290 row(s) affected) Table 'Employee'. Scan count 1, logical reads 9, physical reads 1, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times: CPU time = 15 ms, elapsed time = 105 ms.

    Even after cleaning all the caches and buffers, why is not logical read coming out to be 0 and physical read coming out to be 10 (1 already mentioned + 9 pages of logical read)?

    My understanding of the logical read is the number of pages read from the buffer pool (ram) .

    Can someone help me to undertand that ?

    Thanks

  • December 18, 2022 at 12:23 pm

    #4127106

    I believe your understanding is incorrect.

    logical reads are always there - regardless of having a physical read or not, as pages are first read from disk to ram(buffer pool) and only then they are processed by sql to satisfy the remaining of the query.

     

  • December 18, 2022 at 10:04 pm

    #4127197

    A slightly different way to put it ... I'ts because SQL Server cannot actually use anything directly from Physical Reads.  It MUST be loaded into memory before it can be used and then will produce logical reads as the data is used.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply