Post reply

Memory Usage

  • July 18, 2011 at 12:21 pm

    #253795

    I've got a memory problem. We have 8gb on the server. Normally the server runs around 2-2.5gb. But recently I've been watching the memory slowly creep up throughout the day until it finally maxes out. Every tick of the second hand increases the memory usages of SQL by a mb or so.

    What can I look at in the profiler to help me sort this mess out because as it stands, I'm having to restart the SQL server at the end of the day to clear out the memory. When I run sp_who2, I do not see anything being run that would justify all of the memory allocation.

    Any ideas?

  • July 18, 2011 at 12:26 pm

    #1354412

    Why is the memory usage a problem?

  • July 18, 2011 at 12:33 pm

    #1354422

    because SQL will eventually suck up all the memory and there won't be any free memory.

  • July 18, 2011 at 12:50 pm

    #1354438

    are u running 64 bit sql server ? have you got lock pages in memory enabled , whats the max memory setting under the server properties ?

    Jayanth Kurup[/url]

  • July 18, 2011 at 1:37 pm

    #1354462

    x64. And I've learned something new today. I set the max memory on SQL to 5000mb, which seems to have halted the amount of memory SQL was using. It's not longer increasing, just holding steady. There isn't much going on though on the server, so I don't understand why it is not releasing some of this memory.

  • July 18, 2011 at 1:49 pm

    #1354472

    by design, SQL will use all the memory you care to feed it; so as time goes on, it might keep execution plans longer, since it has the memory to spare, more pages in memory, etc.

    as you've discovered, if you give it a limit, it'll keep itself in check up to that limit, but it will still want to use everything up to it's max.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 18, 2011 at 2:15 pm

    #1354489

    Thanks for the info.

  • July 19, 2011 at 3:37 am

    #1354677

    If you have other things running on your server besides just the Database Engine 5GB may be appropriate but only giving 5GB out of 8GB to SQL sounded low to me. You may be leaving some performance potential on the table with that setting. Here is an article I pass along quite often with some guidelines for setting max memory:

    http://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/[/url]

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • July 19, 2011 at 8:45 am

    #1354864

    I moved it up to 6gb last night after I did some maintenance. That gives me 2gb for the OS and the ERP. I'll just watch it for a while and see how it goes.

  • July 19, 2011 at 3:42 pm

    #1355191

    I would not worry about this at all. SQL Server gradually reserves whatever RAM is available to it. This is by design.

    Most SQL Servers are dedicated to only being a database server, so it really should not be an issue.

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

    When you start SQL Server, SQL Server memory usage may continue to steadily increase and not decrease, even when activity on the server is low. Additionally, the Task Manager and Performance Monitor may show that the physical memory available on the computer is steadily decreasing until the available memory is between 4 to 10 MB.

    This behavior alone does not indicate a memory leak. This behavior is normal and is an intended behavior of the SQL Server buffer pool.

  • July 19, 2011 at 3:55 pm

    #1355200

    This one is not a dedicated SQL server though. What raised my concern is 100% memory being used and nothing was available. Right now with the 6gb max on SQL, I'm using another 1gb for other apps and the OS and I have 1gb free at the moment. If it stays like that, I'll be OK.

  • July 19, 2011 at 4:00 pm

    #1355203

    adams.squared (7/19/2011)

    This one is not a dedicated SQL server though.

    If that's the case then you need to drop SQL's max memory down until there's enough free memory for the other apps and the OS.

    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

  • July 19, 2011 at 5:47 pm

    #1355250

    Hi

    Well , by default SQL will use all your memory. SQL don't do things out of the ordinary it does what we tell it to do.

    Be default memory setting will be 2TB. or thereabouts.

    Unless you are Rockerfellar, you might want to change this.

    sp_configure 'max server'

    Change it to around 512mb for OS , and 512 MB / 4 GB of ram. So on 8 Gb I'd give my SQL 7GB. If it's x64, if it's 32bit I'd give SQL a bit more and the OS a bit less .

    SQL is not going to boot things out of memory if there is no pressure to do so. So if BOB queried a table yesterday without a where clause and used all your 8GB it's still going to sit there today. The nice thing to do is adjust the memory and keeping an eye on task manager, see how SQL dynamically drop the memory to comply with your new setting. Very nice.

    Now that your OS can breathe you can run some perf counters, such as page life expectancy and buffer cache hit ratio, this will indicate if SQL indeed need more memory. If it does, you also simply can't go out an buy more. You might then need to run some queries.

    Cheers

    Jannie

Viewing 13 posts - 1 through 12 (of 12 total)

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