SQL Server Memory Management Explained

  • SQLBOT

    SSCrazy Eights

    Points: 8014

    Comments posted to this topic are about the item SQL Server Memory Management Explained

  • Nakul Vachhrajani

    SSChampion

    Points: 10220

    Good article, Craig! Learnt a few new things today with respect to troubleshooting memory pressure on the server.

    Thank-you for taking the time out to compile your findings and write the article.

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com
    Be courteous. Drive responsibly.

    Follow me on
    Twitter: @sqltwins

  • GregoryLPalmer

    SSC Rookie

    Points: 46

    Nice article, I noticed one issue. You refer to the program you're running as process monitor in the article but I believe it is actually Process Explorer. Lost some time tracking down that issue since I haven't used either one in a long time. If you want to throw in a link, it is here on the TechNet site. I also noticed that the interface has changed in the new version.

  • stuart-717928

    SSC Rookie

    Points: 42

    I've just installed the latest version of Process Monitor 2.96 and cannot find an option to show the system information as you mention. Would you perhaps elaborate for us as there is no View option in this version of the program.

  • GregoryLPalmer

    SSC Rookie

    Points: 46

    stuart-717928 (9/20/2011)


    I've just installed the latest version of Process Monitor 2.96 and cannot find an option to show the system information as you mention. Would you perhaps elaborate for us as there is no View option in this version of the program.

    I linked the correct program in my post above.

  • giles.middleton

    Valued Member

    Points: 57

    I believe there's an error in the article, circa 4,700,000 K is not 4.7MB, it's 4.7GB.

    "The Available counter will show how many KB are available to the host;400 MB is probably as low as you would want to see it. The image above shows ~4.7 MB in the Available counter"

  • M&M

    SSC-Insane

    Points: 21699

    Good article. Thanks.

    M&M

  • Sailor

    SSCertifiable

    Points: 5221

    Can you clarify that current should be less than available?

    See mine...

  • Lucas Beris

    SSC Rookie

    Points: 29

    Hi, great article! I have a doubt when you say:

    When the Current counter is larger than your Available counter it indicates that you are paging to disk

    Isn´t part of the current memory already used by my process, that's why the available memory is lower? And maybe my process is not swapping to disk?

    Thanks,

    LKZ:

  • swellguy

    Mr or Mrs. 500

    Points: 526

    This was excellent; I appreciate your detailed sources, as well.

  • SQLBOT

    SSCrazy Eights

    Points: 8014

    Lucas Beris (9/20/2011)


    Hi, great article! I have a doubt when you say:

    When the Current counter is larger than your Available counter it indicates that you are paging to disk

    Isn´t part of the current memory already used by my process, that's why the available memory is lower? And maybe my process is not swapping to disk?

    Thanks,

    LKZ:

    thanks for the question.

    I believe I meant to write :

    When the Current counter is larger than your Total counter

    I'll need to edit the article later.

  • SQLBOT

    SSCrazy Eights

    Points: 8014

    GregoryLPalmer (9/20/2011)


    Nice article, I noticed one issue. You refer to the program you're running as process monitor in the article but I believe it is actually Process Explorer. Lost some time tracking down that issue since I haven't used either one in a long time. If you want to throw in a link, it is here on the TechNet site. I also noticed that the interface has changed in the new version.

    Good Catch Greg.

    Critical Error.

    I will update it later

    Yes I intended to say "Process Explorer".

    http://technet.microsoft.com/en-us/sysinternals/bb896653

    I almost always say the wrong one when I mean the other.:blush:

  • jerome.landis

    Old Hand

    Points: 307

    Yes, i have the situation where the current memory is 17.8GB and the total mem is 16.6GB.

    How do i correct this situation?

    Question 2# on your screen shot, the page faults delta is 13891, I guess you had an issue before the screen shot? How did you fix or create the issue?

  • SQLBOT

    SSCrazy Eights

    Points: 8014

    jerome.landis (9/20/2011)


    Yes, i have the situation where the current memory is 17.8GB and the total mem is 16.6GB.

    How do i correct this situation?

    Question 2# on your screen shot, the page faults delta is 13891, I guess you had an issue before the screen shot? How did you fix or create the issue?

    answer #1

    The gut instinct is to reduce the amount of memory that the machine is using (maybe give SQL Server less using the max server memory setting... check the page life expectancy on your instances).

    Either that or install more memory. you are in a 'bad place' right now. I would imagine someone has noticed some performance issues before now... although keep monitoring the situation... it could be that you were running dbcc checkdb and reindixing and updating statistics all at once on 3 different instances... check to see how much is system cache, how much SQL is using You'll discover your course as you discover more information. Feel free to private message me if you have some other questions on the topic.

    #2. Page faults are normal, but the screenshot was from my laptop, not a server. I don't know what I was doing around then. It looks like I was working hard! 🙂

  • SQLBOT

    SSCrazy Eights

    Points: 8014

    giles.middleton (9/20/2011)


    I believe there's an error in the article, circa 4,700,000 K is not 4.7MB, it's 4.7GB.

    "The Available counter will show how many KB are available to the host;400 MB is probably as low as you would want to see it. The image above shows ~4.7 MB in the Available counter"

    Typo, thanks!

    I'll get it fixed.

Viewing 15 posts - 1 through 15 (of 20 total)

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