none
Question about sql 2005, x64, SP1 memory use......

    Question

  • We have a sql 2005 (x64) server with 6GB of RAM (total box RAM: 8GB) running fine for year. The OS is Windows 2003 R2, x64. Last night we boosted the box to 32GB of RAM and gave Sql Server 24GB.  To my surprise this morning I found that sql (per Task Manager) was using its cap of 24GB! There isn't that much processing going on on that box and I thought that sql would only request needed memory (up to its cap).  It appears that sql initially grabs all the RAM it can up to its cap. Which version of the "truth" is the truth?

    TIA,

    edm2

    P.S. And is this behavior the same in sql 2008+?

    P.P.S We have a specific, old,  application that runs on the box preventing us from upgrading to later Sql SPs


    • Edited by edm2 Tuesday, October 15, 2013 7:31 PM
    Tuesday, October 15, 2013 7:29 PM

Answers

  • Hello EDM,

    Adding to what Sean and Tom mentioned

    >>To my surprise this morning I found that sql (per Task Manager) was using its cap of 24GB!

    That is wrong place to look for memory consumed by SQL server if locked pages in memory privilege is there for SQL server service account.It only shows you working set and not the memory locked by  LPIM privilege.I would suggest you to first give (if not present) LPIM privilege to SQL server service account so that SQL memory cannot be paged .

    I use below link to get SQL server memory used in 2005

    http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/28/t-sql-script-to-monitor-memory-usage-by-sql-server-instance.aspx

    PS: I totally agree with Sean and Tom its behavior what you are seeing

    PPS: please update your SQL server 2005 to SP4 AFAIK SP1 is not supported


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


    • Edited by Shanky_621 Wednesday, October 16, 2013 5:58 AM
    • Marked as answer by edm2 Wednesday, October 16, 2013 6:11 AM
    Wednesday, October 16, 2013 5:57 AM
  • yes that true what everyone said, but first you would have been checked how much really the sql is using as Task Manager you cannot believe much

    Counters that you need to look

    SQLServer:Memory Manager:Target Server Memory(KB) - Total amount of dynamic memory the server is willing to consume
    SQLServer:Memory Manager:Total Server Memory(KB)   - Total amount of dynamic memory the server is currently consuming

    based on that if really using then you can check inside of sql what else is going on:--------Determine SQL Server memory use by database and object

    http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/

    Also it can helps incase if  you need other information.

    How to Prove Your SQL Server Needs More Memory [Video]
    http://www.brentozar.com/archive/2013/09/how-to-prove-your-sql-server-needs-more-memory-video/


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    • Marked as answer by edm2 Wednesday, October 16, 2013 1:58 PM
    Wednesday, October 16, 2013 7:48 AM

All replies

  • Hello,

    This is very well documented and correct behavior. SQL Server will use as much memory as you let it use, setting the max memory for 2008R2 and below sets the maximum size of the buffer pool, for 2012 and above sets for most things including the buffer pool.

    The way SQL Server works is to keep as much in memory as possible so that fast access can be given and less of an IO subsystem hit. Even if the data is not actively being 'used' at some point a query had to touch it which is why it is in the buffer pool. If the max server memory was set to 128 GB (assume a server had 128 GB of memory) and there were no databases or activity on the instance, only a minimal amount of memory would be used. Since this is an active instance and more memory was given, there was no reason to age or lazy write out any of the items in cache. Thus the memory was used as there was always more free memory to satisfy requests.

    The "truth" is that SQL Server will not "immediately grab" memory, it will use it as long as there is free memory to satisfy user activity. You can easily reproduce these results yourself. Start up a new instance, set the perfmon counters on the server and the instance to check memory. Don't do anything, the memory will wobble a little but stay relatively still. Now simulate user load by running queries against a database. Notice the ram usage continue to rise until the max server memory limit is hit.

    -Sean


    Sean Gallardy | Blog | Twitter

    • Proposed as answer by vr.babu Wednesday, October 16, 2013 5:46 AM
    Tuesday, October 15, 2013 7:36 PM
    Answerer
  • This is completely normal and expected behavior.

    Please see:

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

    • Proposed as answer by vr.babu Wednesday, October 16, 2013 5:46 AM
    Tuesday, October 15, 2013 7:56 PM
    Moderator
  • Sean, you wrote "...Notice the ram usage continues to rise until the max server memory limit is hit."

    OK, but the server was doing fine with 6GB of RAM. I can't begin to imagine what queries running on that box could possibly use, or "touch" upon 28GB of RAM.  This is not a DW database, it's  OLTP. Yes we have some complicated transactions but using 28GB of RAM seems absurdly high.

    edm2

    Tuesday, October 15, 2013 11:20 PM
  • Hello,

    OK, but the server was doing fine with 6GB of RAM. I can't begin to imagine what queries running on that box could possibly use, or "touch" upon 28GB of RAM. This is not a DW database, it's OLTP. Yes we have some complicated transactions but using 28GB of RAM seems absurdly high.

    Do you have at least a database or combination of databases that are greater that 28GB in size? If so, you've answered your own question. Just because it's "OLTP" doesn't mean the data can't live in memory. Have a customer table with 500,000 people and someone ran an email campaign? What's the size of that table - it's now in memory. Unless there is memory pressure, that's where it's going to stay. This isn't a bad thing!

    My second thought is... if it ran fine with 6GB of memory, why was more added? IF memory pressure wasn't detected it seems like a wasted expense.

    -Sean


    Sean Gallardy | Blog | Twitter

    Wednesday, October 16, 2013 1:06 AM
    Answerer
  • Sean wrote

    >>> Do you have at least a database or combination of databases that are greater that 28GB in size?

    Yes.  There are only two databases on the server, each around 50GB!>>>

    >>>  Unless there is memory pressure, that's where it's going to stay. This isn't a bad thing!

    You are right, that should speed up processing but I guess it bothers me to see that only 25% (of 32GB) is free at any moment of the day. I feel confined, restricted, not much room for growth whereas "80%" of total available RAM free makes me feel happier!

    >>> My second thought is... if it ran fine with 6GB of memory, why was more added?

    I'm not sure but I think that Networking's server monitoring utility indicated the server needed more RAM (OS paging?) so by the time I heard about it they had already purchased the RAM. Guess I can't complain but I'm don't know if it was worth the money.

    edm2

    Wednesday, October 16, 2013 5:39 AM
  • Hello EDM,

    Adding to what Sean and Tom mentioned

    >>To my surprise this morning I found that sql (per Task Manager) was using its cap of 24GB!

    That is wrong place to look for memory consumed by SQL server if locked pages in memory privilege is there for SQL server service account.It only shows you working set and not the memory locked by  LPIM privilege.I would suggest you to first give (if not present) LPIM privilege to SQL server service account so that SQL memory cannot be paged .

    I use below link to get SQL server memory used in 2005

    http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/28/t-sql-script-to-monitor-memory-usage-by-sql-server-instance.aspx

    PS: I totally agree with Sean and Tom its behavior what you are seeing

    PPS: please update your SQL server 2005 to SP4 AFAIK SP1 is not supported


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


    • Edited by Shanky_621 Wednesday, October 16, 2013 5:58 AM
    • Marked as answer by edm2 Wednesday, October 16, 2013 6:11 AM
    Wednesday, October 16, 2013 5:57 AM
  • yes that true what everyone said, but first you would have been checked how much really the sql is using as Task Manager you cannot believe much

    Counters that you need to look

    SQLServer:Memory Manager:Target Server Memory(KB) - Total amount of dynamic memory the server is willing to consume
    SQLServer:Memory Manager:Total Server Memory(KB)   - Total amount of dynamic memory the server is currently consuming

    based on that if really using then you can check inside of sql what else is going on:--------Determine SQL Server memory use by database and object

    http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/

    Also it can helps incase if  you need other information.

    How to Prove Your SQL Server Needs More Memory [Video]
    http://www.brentozar.com/archive/2013/09/how-to-prove-your-sql-server-needs-more-memory-video/


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    • Marked as answer by edm2 Wednesday, October 16, 2013 1:58 PM
    Wednesday, October 16, 2013 7:48 AM