none
SQL SERVER MEMORY ISSUE

    问题

  • Hi,

    We're recently having memory issues with our server which has SQL Server. Here's our server's general properties.

    Product: Microsoft SQL Server Standard Edition

    OS: Microsoft Windows NT 6.0 (6002)

    Platform: NT INTEL X86

    Version: 10.0.1600.22

    Memory: 1977 (MB)

    Our system type is 32-bit with only 2 GB RAM. I've tried to set the max server memory to 1000 MB but to no avail. Still the memory used as displayed in the task manager is always 1.78 GB or more, and not getting lower. Appreciate your help. Thanks!

    2012年4月30日 7:16

答案

  • Francis,

    Normally when we have dedicaed SQL Servers, no other services or applications are run on them. Sometimes this just can't be the case at smaller businesses where hardware and software are at a premium.

    Some things I would like to point out:

    1. The server has 2 GB of ram for SQL Server, RDP sessions, Mirroring, and other programs or services running on it. This is very low in my opinion. Either dedicate the machine solely to SQL Server or add memory. If neither are an option, put the max server memory much lower, in the realm of 512 MB and see if that helps at all. There is a good chance that SQL Server is being paged and not actually the culprit.

    2. For the page swap check your IO for excessive consumption with perfmon and also add in the paging file % usage, see if it changes drastically over time given a server reboot to the point of high usage.

    3. Double check your wait stats to see what SQL Server is waiting on. Granted you had said there were no performance issues, but it may help to show where an issue may be. For example, if you have high latch waits, that's indicitive of memory issues, coupled with a high page file usage, etc may give you the information needed to get more memory for the server (I'm not saying memory, it could be anything just using that an as example).

    sys.dm_os_wait_stats: http://msdn.microsoft.com/en-us/library/ms179984.aspx

    Nice post on wait stats: http://www.mssqltips.com/sqlservertip/1949/sql-server-sysdmoswaitstats-dmv-queries/

    -Sean


    Sean Gallardy, MCC | Blog

    2012年5月2日 13:48
    答复者

全部回复

  • Hello,

    The max server memory value is only for the buffer pool, there is memory that SQL Server uses outside of that. It is kind of a misnomer as it's not the maximum amount of memory that SQL Server can use.

    Even though it is 2 GB, it is good habit to use perfmon to check memory values on a 32 bit system.

    Are you having a slowdown in performance because of this? Page file swap going crazy? Are there any other application that run on the server or any other services that are serving to end users?

    Items that will be located outside of the buffer pool are things like linked servers, com objects, clr objects, etc. Are any of these being heavily utilized?

    -Sean


    Sean Gallardy, MCC | Blog

    2012年4月30日 20:10
    答复者
  • Hi Sean,

    Yes, we are always monitoring the system performance and it's always almost reaching the top of the RAM and CPU memory.

    No, we are not having slowdown in performance but sometimes we cannot RDC to our server due to lack of memory (I suppose). I'm not sure about the page swap though, my apologies but could you advise what I need to check regarding this? Yes, we have some softwares running inside and outside the server which connects to the SQL.

    Our SQL server is mirrored to another SQL in different server.

    Thanks so much for your help.

    Francis



    Francis Laborde

    2012年5月1日 7:15
  • Francis,

    Normally when we have dedicaed SQL Servers, no other services or applications are run on them. Sometimes this just can't be the case at smaller businesses where hardware and software are at a premium.

    Some things I would like to point out:

    1. The server has 2 GB of ram for SQL Server, RDP sessions, Mirroring, and other programs or services running on it. This is very low in my opinion. Either dedicate the machine solely to SQL Server or add memory. If neither are an option, put the max server memory much lower, in the realm of 512 MB and see if that helps at all. There is a good chance that SQL Server is being paged and not actually the culprit.

    2. For the page swap check your IO for excessive consumption with perfmon and also add in the paging file % usage, see if it changes drastically over time given a server reboot to the point of high usage.

    3. Double check your wait stats to see what SQL Server is waiting on. Granted you had said there were no performance issues, but it may help to show where an issue may be. For example, if you have high latch waits, that's indicitive of memory issues, coupled with a high page file usage, etc may give you the information needed to get more memory for the server (I'm not saying memory, it could be anything just using that an as example).

    sys.dm_os_wait_stats: http://msdn.microsoft.com/en-us/library/ms179984.aspx

    Nice post on wait stats: http://www.mssqltips.com/sqlservertip/1949/sql-server-sysdmoswaitstats-dmv-queries/

    -Sean


    Sean Gallardy, MCC | Blog

    2012年5月2日 13:48
    答复者
  •  It is kind of a misnomer as it's not the maximum amount of memory that SQL Server can use.

    just for information:

    this had changed with SQL Server 2012 where almost all memory used by SQL Server is control by that setting - excluded is the memory used by CLR


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2012年5月3日 21:11