Post reply

Checkpoint Process - Dirty Pages - Memory

  • January 18, 2018 at 9:19 am

    #325497

    Hi All
    Please help me understand something regarding the checkpoint process. 

    When a checkpoint process runs, it writes out the changes to disk and marks the affected pages as clean. So this means that the pages are not actually removed from the buffer pool. Those pages are still taking up space in the buffer pool.
    Is this correct? 
    If so then why is the checkpoint pages/sec counter considered important when assessing memory pressure on SQL Server when it's not actually freeing up space in the buffer pool?

    What am I missing here?

    Thank you.

  • January 22, 2018 at 2:03 am

    #1976680

    Hi All
    Can anyone assist with the above?

    Thanks

  • January 22, 2018 at 8:44 am

    #1976739

    Correct - the pages themselves aren't removed.
    Checkpoint pages per sec in and of itself does not necessarily indicate memory pressure. More pages could just as well indicate I/O issues. You would need to monitor other counters, DMVs as well.

    Sue

  • January 22, 2018 at 3:37 pm

    #1976833

    SQLSACT - Thursday, January 18, 2018 9:19 AM

    If so then why is the checkpoint pages/sec counter considered important when assessing memory pressure on SQL Server when it's not actually freeing up space in the buffer pool?

    Because you want the checkpoint to be doing the majority of the writing to disk, and the lazy writer (which *does* free up space) to be close to idle.

    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

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

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