Post reply

SQL Server Memory Change

  • June 27, 2016 at 6:36 pm

    #308735

    Hi guys,

    I encounter 1 issue where my clients told me that the server physical RAM hit 99%. The server have 16GB RAM and using SQL 2008.

    Upon checking, I found that Maximum server memory set in SQL is 10205 MB. This server is running both SQL and other application.

    This application require around 3 GB memory.

    My problem is, can I reduce the maximum server memory to 4096 MB to rectify this issue as it is on production now? I have checked on the application documents that recommend we can just put 1 GB to sql server maximum memory.

    Thank you in advance.

  • June 28, 2016 at 1:58 am

    #1887192

    1GB for SQL's MAX Server memory? Doesn't sound right....

    Have a read of this to help you

    https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

  • June 28, 2016 at 2:20 am

    #1887198

    Well, you have already restricted SQL SERVER on 10 gb. which means u have 6 gb for OS and other thing.

    Upon checking, I found that Maximum server memory set in SQL is 10205 MB. This server is running both SQL and other application.

    This application require around 3 GB memory.

    So your server is App + DB server. so its not the sql server who is causing the memory pressure.

    To be honest you are currently the best person to tell whether your sql server need 10 gb of RAM or not. As BLOB_EATER mentioned above,

    1 GB ram is very low for sql server.

    My recommendation would be your database server and App server should be separated (Costly, but will be very fruitful in longer run).

    And yes i might say we can or not allow and anything else, which i can understand, Try to lower memory slowly (ie set to 9GB/8GB) and monitor your server and memory pressure.

  • June 28, 2016 at 2:50 am

    #1887206

    hadihalim90 (6/27/2016)

    My problem is, can I reduce the maximum server memory to 4096 MB to rectify this issue as it is on production now? I have checked on the application documents that recommend we can just put 1 GB to sql server maximum memory.

    While you can, it's probably a very bad idea. SQL uses memory to improve performance. If you reduce the memory down, you'll see higher CPU, more load on the IO subsystem and most likely slower queries.

    If you currently have SQL set to 10GB, then investigate what else is using that remaining 6GB. Having SQL and app on the same machine is not recommended, because of the resources the app will need. Can you split the app onto another server?

    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

  • June 28, 2016 at 3:23 am

    #1887211

    I dont think it's feasible to split the app and db server. What about the other option such as increasing RAM to 32 GB? will this address the issue..

    Also, is there any tool for me to investigate clearly on what services that are using the rest of the 6GB RAM. I tried task manager, but it looks like if I add up everything it will only be around 4 GB.

    Below is the task manager services and ranmap.

    imgur.com/a/W4l6v

  • June 28, 2016 at 3:38 am

    #1887214

    Can't see the image, but don't use Task Manager. It lies.

    Performance monitor, the process object should help. It'll probably take a lot or work.

    Increasing memory will help, bump to 32, increase SQL's memory to 16 or 18GB and see how things behave.

    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 6 posts - 1 through 5 (of 5 total)

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