none
Memory usage hight RRS feed

  • Question

  • Hello all,

    Few hours back we getting memory usages was critical 90%. I have stopped ssas,ssrs,ssis services because we should not use these services in our box.

    after that memory utilization was 85%. Now we are planning to reduce memory utilization

    what actions we have to take. How can we check where memory utilization was high please suggest me.

    I appreciate your response.

    Regards


    subu

    Thursday, December 26, 2013 3:26 AM

Answers

  • It is normal to have high memory utilization on a SQL Server database server.  The database engine will acquire and hold on to memory to reduce disk I/O and improve performance.  SQL Server will release memory when  detects memory pressure due to other application memory requirements running on the same server.

    You can set max server memory to limit the amount of memory SQL Server uses and avoid thrashing.  Typically this would be the amount of server RAM less about 4 GB for the OS and other application needs.  But you may not need to do this unless you are running other applications (including SSAS, SSRS and SSIS).

     


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, December 26, 2013 3:55 AM
    Moderator
  • Your ram is less then 2 GB?!?

    If so, then this is more of a laptop then a server :-)
    in this case you should use the default settings to allow SQL Server to change its memory requirements dynamically
    http://technet.microsoft.com/en-us/library/ms191144(v=sql.105).aspx


    [Personal Site] [Blog] [Facebook]signature

    Thursday, December 26, 2013 4:17 AM
    Moderator
  •  we cant do any thing we have to work as per client requirement any suggestion how to reduce memory utilization.


    subu

    Hello,

    Unfortunately you cannot do much.My first suggestion would be increase RAM.You have to convince your client or boss .This is your second post reg same issue. And answer is already provided that if 1.66 is RAM.Please dont set max server memory let it be default value.You are facing high utilization because RAM IS LESS IN YOUR SYSTEM.If you cannot increase it nobody can help you and that is truth.

    I am sorry but saying that i cannot go ahead with more RAM is not genuine answer .SQL server to function properly needs some amount of RAM to cache pages if you will deprive SQl server from basic requirements how so you propose it will run better even if you queries are fully optimized.

    Its just like human body we need minimum calories at least to sustain.Same goes for SQL server and other machine .

    Hope this helps


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, December 26, 2013 7:14 AM
    Moderator

All replies

  • It is normal to have high memory utilization on a SQL Server database server.  The database engine will acquire and hold on to memory to reduce disk I/O and improve performance.  SQL Server will release memory when  detects memory pressure due to other application memory requirements running on the same server.

    You can set max server memory to limit the amount of memory SQL Server uses and avoid thrashing.  Typically this would be the amount of server RAM less about 4 GB for the OS and other application needs.  But you may not need to do this unless you are running other applications (including SSAS, SSRS and SSIS).

     


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, December 26, 2013 3:55 AM
    Moderator
  • Hi

    Thank you for you answer.

    My Ram was 1.66GB. Now Sql server.exe memory utilizing was 436208KB

    Could you please suggest us How much we have configure as Max memory for this box.

    Now max memory was we have given 1000.


    subu

    Thursday, December 26, 2013 4:04 AM
  • What operating system and SQL Server version/edition are you running?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    Thursday, December 26, 2013 4:09 AM
    Moderator
  • windows server 2008 R2 Data-center.

    subu

    Thursday, December 26, 2013 4:11 AM
  • Your ram is less then 2 GB?!?

    If so, then this is more of a laptop then a server :-)
    in this case you should use the default settings to allow SQL Server to change its memory requirements dynamically
    http://technet.microsoft.com/en-us/library/ms191144(v=sql.105).aspx


    [Personal Site] [Blog] [Facebook]signature

    Thursday, December 26, 2013 4:17 AM
    Moderator
  • Are you certain the server only has 1.66GB of memory installed?  With all SQL Server editions beyond Express Edition, 4GB is recommended.  After OS requirements, there won't be enough memory left to run SQL Server efficiently with such a configuration.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, December 26, 2013 4:35 AM
    Moderator
  •  we cant do any thing we have to work as per client requirement any suggestion how to reduce memory utilization.


    subu

    Thursday, December 26, 2013 4:50 AM
  •  we cant do any thing we have to work as per client requirement any suggestion how to reduce memory utilization.

    I think you've done as much as you can (set max memory), given the constraints you must work within.  Just be aware that this will only mitigate the memory alarms rather than address the root cause of improper configuration.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, December 26, 2013 5:09 AM
    Moderator
  •  we cant do any thing we have to work as per client requirement any suggestion how to reduce memory utilization.


    subu

    Hello,

    Unfortunately you cannot do much.My first suggestion would be increase RAM.You have to convince your client or boss .This is your second post reg same issue. And answer is already provided that if 1.66 is RAM.Please dont set max server memory let it be default value.You are facing high utilization because RAM IS LESS IN YOUR SYSTEM.If you cannot increase it nobody can help you and that is truth.

    I am sorry but saying that i cannot go ahead with more RAM is not genuine answer .SQL server to function properly needs some amount of RAM to cache pages if you will deprive SQl server from basic requirements how so you propose it will run better even if you queries are fully optimized.

    Its just like human body we need minimum calories at least to sustain.Same goes for SQL server and other machine .

    Hope this helps


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, December 26, 2013 7:14 AM
    Moderator