Post reply

Memory usage

  • November 8, 2011 at 11:31 am

    #246491

    Hi All,

    We have configured mirroring in one of our server. It's having 12 GB RAM.

    Its a dedicated DB server and haven't set MAX memory as well.

    We noticed that sql server always uses almost 9.5 GB of RAM. It never comes down.

    We are expecting it to use less memory during off peak hours. There are no jobs or any other processes running.

    How can I find out what could be the reason ? Do we need to add more memory to the system?

    Thanks in advance.

    Smith.

  • November 8, 2011 at 11:39 am

    #1404745

    Once the ram has been used by the server, it won't release it.

    That's the way it's supposed to happen.

  • November 8, 2011 at 11:24 pm

    #1404967

    Ninja's_RGR'us (11/8/2011)

    Once the ram has been used by the server, it won't release it.

    That's the way it's supposed to happen.

    Is that SO.?

    I was under the impression that SQL Server manages memeory automaticaly when Min and Max memory are not set. When it doesn't need it relaeases the memory to OS... Did I understand it wrongly.? Kindly clarify.

    Thanks for the response.

    Smith.

  • November 9, 2011 at 1:48 am

    #1405022

    SQL Server wont release memory even if it is not using all of it.

    you should leave sufficient amount of memory to the OS for its functioning.

    By using max memory setting, SQL Server will not ask for more memory (when it reaches that limit).

  • November 9, 2011 at 2:36 am

    #1405039

    Joy Smith San (11/8/2011)

    Is that SO.?

    Yes.

    I was under the impression that SQL Server manages memeory automaticaly when Min and Max memory are not set. When it doesn't need it relaeases the memory to OS... Did I understand it wrongly.? Kindly clarify.

    SQl manages automatically no matter what. It'll take memory up to max server memory (or the total memory on the server if that's not set) and only release it (down to min server memory) when the OS requests that it reduce its working set. SQL will not release memory without reason.

    You MUST set max server memory to a sensible figure or SQL can and will take all the memory on the server starving the OS. Doubly important if you're using locked pages

    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

  • November 9, 2011 at 2:40 am

    #1405042

    I am confused on this.

    What if some application ask for more memroy and SQL doesn't need any memory at that time ? It wont still release.?

    Secondly for testing purpose, I configured my local sql server to use MIN memory as 1024 MB. But when I restarted it's using hardly 90 MB it seems (in task manager). Is it because SQL server was not able to acquire that more memory because other applications have already taken rest of the memory .?

    Thanks.

    Smith

  • November 9, 2011 at 3:40 am

    #1405059

    Joy Smith San (11/9/2011)

    Secondly for testing purpose, I configured my local sql server to use MIN memory as 1024 MB. But when I restarted it's using hardly 90 MB it seems (in task manager). Is it because SQL server was not able to acquire that more memory because other applications have already taken rest of the memory .?

    SQL Server doesnt grab min server memory configured. It'll start with as low as possible and will ask OS for more memory if it needs. If it reaches the min memory threshold, it will NEVER come down below the min memory setting.

  • November 9, 2011 at 6:57 am

    #1405131

    Joy Smith San (11/9/2011)

    What if some application ask for more memroy and SQL doesn't need any memory at that time ?

    Then the application asks the OS for memory. The OS doesn't have any spare so asks all running processes (including SQL) to reduce their memory usage. If SQL complies quick enough, all's well. If it doesn't, then the OS will page is out to the swap file (major performance degradation) or, if locked pages are enabled, will throw out of memory errors.

    That's why it's critically important to set max memory to a sensible figure.

    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

  • November 9, 2011 at 10:04 am

    #1405358

    So if we don't set Max memory, SQL will try to take as much memory as possible and wont release it unless there's a request for it from OS.

    SQL start with low memory even if MIN memory is set, gradually takes more memory as needed, and will never release less than the MIN memory.

    So its quite natural to see a server in which SQL server is shown taking large memory, consistently, when MAX is not set and there's no any other application running.

    ... Thank you all. Hope am clear now. Thanks again.

  • November 9, 2011 at 10:14 am

    #1405363

    It's also quite likely to see SQL taking enough memory to starve the OS and cause stability problems if you don't set max server memory.

    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

  • November 9, 2011 at 10:17 am

    #1405368

    GilaMonster (11/9/2011)

    It's also quite likely to see SQL taking enough memory to starve the OS and cause stability problems if you don't set max server memory.

    Thank you very much Gila. Very clear now.

Viewing 11 posts - 1 through 10 (of 10 total)

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