none
SQL Server not using enough memory

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    I have a machine running SQL Server 2008 R2 on Windows Server 2008 Enterprise R2 (64 bit).  The server has 30GB RAM, with SQL Server max memory set at 24GB and min memory at 0.  Whatever demands are placed on the server, total server memory does not rise further than 9GB, even though CPU and page life expectancy both appear to be suffering as a result. 

    SQL Server is the only major process running on the server.  Neither task manager nor process explorer reveal any other major processes, but task manager shows 29.8 GB physical memory usage.

    Is there anything further at the OS or SQL Server configuration level that I should do to prompt it to use more memory?

    Any help greatly appreciated,

    Ed.


    EDIT:  Setting min server memory above 9GB has no effect.
    • Edited by Ed Allison Thursday, October 27, 2011 4:38 PM
    Thursday, October 27, 2011 4:34 PM
    |
    |

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    We were eventually able to trace this to a faulty driver on the server.

    Help much appreciated.

    Ed.

    • Marked as answer by Ed Allison Friday, October 28, 2011 10:14 AM
    Friday, October 28, 2011 10:14 AM
    |
    |

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    Hello,

    Go ahead and set 'Lock pages in Memory' http://msdn.microsoft.com/en-us/library/ms190730.aspx

    I hope you are holding Enterprise version of SQL Server,if its standard then you will need to enable a trace flag for Lock pages in memory.

    http://support.microsoft.com/kb/970070

    Thank you,

    Anup | Forum Support| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer.
    Thursday, October 27, 2011 4:55 PM
    |
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you very much - I'll give that a try.

    Thursday, October 27, 2011 5:02 PM
    |
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Sorry to query this, but when I check your link, it says that this setting is not necessary on 64 bit, which this machine is on.  Should I still try it?

    Thanks,

    Ed.

    Thursday, October 27, 2011 5:04 PM
    |
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    Great question and this was indeed a serious debate since BOL still says that we dont need it.

    Glenn Berry has a good article on this  and you can find it here http://sqlserverperformance.wordpress.com/2011/02/14/sql-server-and-the-lock-pages-in-memory-right-in-windows-server/

     

    Thank you,

     

    Anup | Forum Support| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer.
    Thursday, October 27, 2011 5:44 PM
    |
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    How are you determining SQL is not using more than 9GBs?  Task Manager does not show all memory used by SQL Server.

     

    Thursday, October 27, 2011 7:27 PM
    |
    |
    Moderator
  • Question
    Sign in to vote
    1
    Sign in to vote
    Maybe SQL Server does not need more than 9 Gb on your server. This really depends on your database size and the workload / access. If the total memory required (database buffers, plan cache etc etc) does not exceed 9 Gb, SQL Server will not claim it from the OS. Take a look at DBCC MEMORYSTATUS to check this.
    Thursday, October 27, 2011 7:48 PM
    |
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi Ed,

     In Task Manager make certain that there is a check next to Show processes from all Users. It sounds as though you are looking at the processes tab, seeing 9GB being used by SQL server, nothing else using that much, but total server memory use is near max? In other words, adding up Mem Usage on the Processes tab in Task Manager does not equal the total being used. You should see another process using the rest of your memory.

    Once you are out of memory, you will see your cpu and I/O rise dramatically as paging occurs. You can't get SQL to take memory from the OS, only the other way around, which is the discussion AnupSivaDas provided. If the OS has memory demands, it will force SQL to begin paging out unless Lock Pages in Memory is enabled. As the box is using the total physical memory, but SQL is only reporting 9 GB, this may be happening.

    You can enable Lock Pages in Memory to prevent the OS from forcing paging from SQL, but at the expense of the OS. This could bring the box to a halt.

    As a troubleshooting step, you could enable Lock Pages in Memory to see if your SQL memory used climbs above 9 GB. However, you will still need to investigate the process that is causing the demands from the OS.

    Thursday, October 27, 2011 9:22 PM
    |
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Tom, the main reason the we are assuming this is that although memory use does not increase, CPU usage goes much higher than our other servers with comparable work load and page life expectancy goes much lower.  This makes us think that it is paging because of what it interprets as a shortage of memory.

    Thank you for your reply,

    Ed.

    Friday, October 28, 2011 5:02 AM
    |
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    George, we do have 'Show processes from all Users' checked, but still no substantial processes show other than SQL Server.  Thank you for your guidance regarding Lock Pages.  I will proceed with caution.

    Ed.

    Friday, October 28, 2011 5:04 AM
    |
    |
  • Question
    Sign in to vote
    1
    Sign in to vote

    "This makes us think that it is paging because of what it interprets "

    Open up perfmon .. lookup the page file usage for SQL server process in real time and be sure.

    LPIM is something thats beneficial if you see working set size trimming.. not otherwise. If SQL is capped to 24 gigs (on a server with 30gigs) its unlikely that you will never need it.

    My suggestion - Add more workload and look at the total server memory in perfmon.

    http://msdn.microsoft.com/en-us/library/ms190924.aspx

    Thanks - Vijay Sirohi
    Friday, October 28, 2011 5:34 AM
    |
    |
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    We were eventually able to trace this to a faulty driver on the server.

    Help much appreciated.

    Ed.

    • Marked as answer by Ed Allison Friday, October 28, 2011 10:14 AM
    Friday, October 28, 2011 10:14 AM
    |
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi!

    Care to share which driver?

    br

    /Linus

    Thursday, December 06, 2012 7:38 PM
    |
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi,

    Probably sql server may not be require more memory. Please check is there lot of processes running on the instance. Eventhough you allot lot of

    memory, the sql server will not hold it actually. It will release excess memory to other process runing in OS

    best regards,

    Sengwa

    Best regards, Sengwa

    Friday, December 07, 2012 3:17 PM
    |
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    Which driver was that, Ed ?

    Donny

    Sunday, October 06, 2013 7:30 PM
    |
    |