What is a read ?

  • Hi All

    When using DMV's to assess your SQL instance, what is a read?

    For example, when dealing with DMV's like:

    sys.dm_exec_query_stats

    sys.dm_exec_requests

    There are columns like logical_reads, physical_reads etc...

    My questions is, is a read when SQL reads one page?

    Thanks

  • a logical read is the pages that are read in memory, a physical read is when the page is brought in from disk

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" πŸ˜‰

  • Perry Whittle (10/6/2012)


    a logical read is the pages that are read in memory, a physical read is when the page is brought in from disk

    Thanks

    Does 1 read mean 1 page?

  • Yes a read is a page. A logical read may need to initiate a physical read first if the requested page is not in cache.

    more info may be found at this link

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" πŸ˜‰

  • I've always gone by Microsoft's explanation:

    β€œThe I/O from an instance of SQL Server is divided into logical and physical I/O. A logical read occurs every time the database engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read is then performed to read the page into the buffer cache. If the page is currently in the cache, no physical read is generated; the buffer cache simply uses the page already in memory.”

    Also, keep in mind that when you run a trace it is considered a logical read and not physical.

    Physical reads for me have been a pain point when dealing with performance.

  • CYates (10/8/2012)


    I've always gone by Microsoft's explanation:

    ...

    {snip}

    Also, keep in mind that when you run a trace it is considered a logical read and not physical.

    Have you got an MS link that explains that as well?

    --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)

  • Never mind. I got it. BOL states:

    Reads

    The number of read operations on the logical disk that are performed by the server on behalf of the event. These read operations include all reads from tables and buffers during the statement's execution.

    In this case, I don't believe that "logical" means just "logical reads". It would appear to include both physical and logic reads for the "logically named disk".

    Of course, I could have misinterpreted that. Any one else have something more finite?

    --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)

  • Ah... got it. If found (and immediately forgot to copy the URL for) a post by Microsoft Certified Master Gail Shaw where she explained that SQL Server actually only reads from the "buffer pool" which is all "logical". Reads from the physical disk are always read into the "Buffer Pool". It's the reads from that "Buffer Pool" that SQL Profiler counts.

    It would appear that the only way to easily find out if physical reads were actually involved is to SET STATISTICS IO ON before you run the query.

    --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)

  • Jeff Moden (10/8/2012)


    CYates (10/8/2012)


    I've always gone by Microsoft's explanation:

    ...

    {snip}

    Also, keep in mind that when you run a trace it is considered a logical read and not physical.

    Have you got an MS link that explains that as well?

    Yes, the link i posted above πŸ˜‰

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" πŸ˜‰

  • Jeff Moden (10/8/2012)


    Reads from the physical disk are always read into the "Buffer Pool". It's the reads from that "Buffer Pool" that SQL Profiler counts.

    Easiest way to think about it is that all reads are logical. Some of the logical reads may additionally be physical reads (the page required wasn't in the buffer pool)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (10/8/2012)


    Jeff Moden (10/8/2012)


    Reads from the physical disk are always read into the "Buffer Pool". It's the reads from that "Buffer Pool" that SQL Profiler counts.

    Easiest way to think about it is that all reads are logical. Some of the logical reads may additionally be physical reads (the page required wasn't in the buffer pool)

    Is it safe to say that when a page is requested and it's not in the buffer pool, 2 reads are happening, 1 Physical(To bring the page into memory) and 1 logical (to read the page in memory)?

    Thanks

  • SQLSACT (10/8/2012)


    Is it safe to say that when a page is requested and it's not in the buffer pool, 2 reads are happening, 1 Physical(To bring the page into memory) and 1 logical (to read the page in memory)?

    Thanks

    Thats exactly what is happening.

    The physical read just go grabs the page, the logical read is interested what's on the page.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" πŸ˜‰

  • GilaMonster (10/8/2012)


    Jeff Moden (10/8/2012)


    Reads from the physical disk are always read into the "Buffer Pool". It's the reads from that "Buffer Pool" that SQL Profiler counts.

    Easiest way to think about it is that all reads are logical. Some of the logical reads may additionally be physical reads (the page required wasn't in the buffer pool)

    Thanks, Gail.

    --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)

  • Perry Whittle (10/8/2012)


    Jeff Moden (10/8/2012)


    CYates (10/8/2012)


    I've always gone by Microsoft's explanation:

    ...

    {snip}

    Also, keep in mind that when you run a trace it is considered a logical read and not physical.

    Have you got an MS link that explains that as well?

    Yes, the link i posted above πŸ˜‰

    That's what I get for reading threads without the right amount of caffeine in my system. I totally missed that. Thanks Perry.

    The key part on the article from that link is the very first paragraph.

    The I/O from an instance of the SQL Server Database Engine includes logical and physical reads. A logical read occurs every time the Database Engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read first copies the page from disk into the cache.

    --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 14 posts - 1 through 13 (of 13 total)

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