Post reply

Logical Reads x Buffer Pool Content

  • January 3, 2017 at 5:00 am

    #316578

    Hi All.

    I'm troubleshooting a specific query performance, and one of the first thing that i do is to check the statistics IO output.

    Below is one of the lines from statistics io result

    Table 'XXXXXX'. Scan count 0, logical reads 571960, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    But when i look into the buffer pool content (using sys.dm_os_buffer_descriptors), the total pages is 7.941 for the same table.

    if i run the same query again, the result is the same (statistics io reports a lot of logical reads , but the buffer descriptors contains far less pages).

    Does anyone know why these values are so different?

    I'm using sql server 2014 SP2 enterprise edition.

    Thanks,

    Luiz

  • January 3, 2017 at 5:07 am

    #1920324

    Pages can be read multiple times. If a page is read 5 times during a query's processing, that's 5 logical reads.

    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

  • January 3, 2017 at 5:16 am

    #1920326

    GilaMonster (1/3/2017)

    Pages can be read multiple times. If a page is read 5 times during a query's processing, that's 5 logical reads.

    Hi Gail!

    Thanks for the reply and for the help.

    it clarifies me a lot.

    Regards,

    Luiz

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

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