Post reply

Lazy Writer Activity

  • December 9, 2013 at 6:11 am

    #302231

    Hi all

    In a system with no writes and just selects, lazy writer won't be doing anything at all? Correct?

    In a system like this, which SQL process makes sure that there are free buffers for all the selects?

    Thanks

  • December 9, 2013 at 7:50 am

    #1671949

    The same process will write dirty pages to disk and free clean pages when they pass the least recently used threshold.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 10, 2013 at 12:56 am

    #1672219

    Thanks

    The Lazy Writes/Sec counter monitors the amount of dirty pages are moved from to disk. Which counter, if any, measures the total amount of pages, including clean pages moved to disk?

  • December 10, 2013 at 3:10 am

    #1672233

    Clean pages aren't moved to disk. The only pages that are written back to disk are dirty pages, so the lazy writes/sec measures the total number of pages per sec that the lazy writer writes to disk.

    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

  • December 10, 2013 at 3:16 am

    #1672235

    Ok - my wording was wrong

    Clean pages can be removed dropped from memory by the lazy writer, based on LRU.

    My question is, I use the lazy writes/sec counter as one of the tools used to monitor memory pressure. I can expect to see this counter do nothing on a system with just selects, correct? So is there a similar counter that shows the amount of clean pages being removed from memory?

    Thanks

  • December 10, 2013 at 3:24 am

    #1672236

    Primarily as a measure of load you can look at page faults/sec. That will tell you how many times SQL Server had to go looking for pages within memory. You can also read pages in/sec to see how much stuff is getting read from disk.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 10, 2013 at 4:29 am

    #1672251

    Just be careful which pages/sec type counter from which object. Many have to do with reads from the page file, which is not what you're looking for.

    I'd monitor the free list stalls/sec to start with. Not by itself though. With all the other usual memory-related counters, maybe with page reads/sec

    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

  • December 10, 2013 at 4:33 am

    #1672255

    Thanks Grant and Gail

Viewing 8 posts - 1 through 7 (of 7 total)

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