October 13, 2011 at 4:17 pm
Hi,
i am facing memory Consumption problem in my new test machine that having 48 GB of RAM.and its fully utilized by sql server. I got alert from system that my memory utilization is 100%
I checked and found sql server got 47.2 reserved then i restart sql service but after restart the same thing happened.
i am wondered why sql server suck all memory please guide me how i could manage this situation.
my server configuration is below.
Sql server 2005 64 bit (compatibility level is 80 due to application dependency).
Windows 2008 R2 64 bit
2 node active/active cluster
Physical Ram = 48 GB
Min Memory Assigned = 0
Max Server memory = 2 TB default
i would appreciate if you guide me to resolve above problem.
October 13, 2011 at 5:07 pm
It's the default (& correct) behavior for sql server. It just eats everything you give to him and keep it for itself. This is because ram is the best defense against IO problems.
On a 48 GB server I'd probably limit the server to 42 GB of ram and then see to make sure that the OS is not starved and starts paging to disk.
Adjust up and down untill windows is happy, but at the extreme low limit.
Now keep in mind that stuff like XML, clr takes ram outside of the limit you impose to sql server.
The only way to know your real needs is to test under real load and keep monitoring.
October 13, 2011 at 5:24 pm
Thanks for your prompt reply , if i got the right point from you that i need to setup the min and max server memory around 42 GB out of 48 GB.
please correct me if i am wrong .
Moreover , there is no load on DB then why its consuming full of memory ?? i am afraid when this server go to production then how much memory it will consume . please guide me
Thanks in advance.
October 13, 2011 at 5:36 pm
Yes set the max memory for sql server around 42 GB.
Since there are only 48 GB on the box, the server can't possibly ask for more than that ;-).
I understand your reasoning, but you are a little too early in the process to hit the panic button.
Once the server is online check the wait stats to see if you really have a problem with any ressources on the machine and come back for help if need be.
October 14, 2011 at 1:48 am
Thanks for your precious comments !
October 16, 2011 at 1:42 am
Hi,
As per your recommendations i have configured 42 GB of RAM of my server1 out of 48 GB.
i restarted the services and checked every thing is running fine but when i see the task manager it showing 42 GB of RAM consumed by sql server
even no one is connected with this server.
and i have done the same above activity to other server2 the task manager showing 4.5 GB is consuming and its not taking full 42 GB RAM .
i am still not cleared about the above server1 configuration , after restart the service suddenly took full 42 gb of RAM ,
but it should not like that ???? is that dynamically assigned for that server ??
i need your expert view to resolve this issue
please guide me i will very thankful to you.
October 16, 2011 at 6:29 am
My bad here. I missed the active / active part.
If you have 2 servesr running at the same time on the same machine you probably need to limit both of them to ±20 GB.
I say probaby here because I never had to use that setup but this make the most sense to me at the moment.
October 16, 2011 at 8:14 am
After you restart SQL Server, it will start allocating memory as needed - up to the max memory setting. Once it hits the max memory setting, it will not take any more but it also will not give it back unless the OS requests it.
If you have locked pages in memory set - then SQL Server will lock that memory and not return it back to the OS unless under extreme memory pressure.
Your situation is normal and expected for SQL Server.
Note: do not set min memory equal to max memory. Doing so can cause memory pressure issues within SQL Server.
If you have more than one instance running on the same system, then you need to balance the memory. This is where you need to set both min and max memory settings, where the min memory is the minimum amount of memory each instance needs - and max memory is what each instance can use.
With a multi-instance server, you need to be careful with setting locked pages in memory right. If you set that for each instances service account - the memory taken by each instance will be locked and that could cause additional issues.
To make it simple, normally you would set max memory on each instance to add up to a total of available memory (minus the memory you need for the OS). On a system with 48GB of memory, you would leave at least 4GB to the OS and up to 8GB depending on what else is running (e.g. SSIS, SSRS, .NET CLR, etc...).
It is possible to over-commit on max memory, but then you have to be very careful with setting lock pages in memory right. If you do not set that and over-commit on max memory for each instance - then SQL Server could (potentially) manage the additional memory and swap between each instance as that instance needs more memory.
The problem with doing that is that you could end up starving the OS or starving the other instance(s) because SQL Server may not release the memory in a timely enough manner - or at all, depending on system utilization.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 17, 2011 at 7:15 am
Hi Jeff,
Thanks for your valuable explanation.. I have got below question in my mind, i would appreciate if you could explain me 🙂
I have a server with two instances and and 20 GB of RAM, I gave 8GB and 8GB for two SQL Instances and 4 GB for OS.
1. Here I am using two different service accounts for two instances and both were added in Lock Pages in Memory. Was it a bad idea?
2. One of the SQL Instance is running with SSAS will it use memory from 4 GB allotted for OS or from relevant SQL Instance?
3. If I want to provide enough room for .net clr and SSIS and SSAS do I need to specifically configure memory anywhere? I have configured for one server MemToLeave where I experience memory pressure with CLR functions, was it correct configuration?
I've used -g512 startup parameter to configure MemToLeave, do I need to configure this for every server where I am using .net CLR functions or etc..
Please let me know, I would appreciate for your valuable time and efforts.
October 17, 2011 at 7:21 am
I'll let Jeff take that one... but it looks like you're missing key details in your question about GB on the machine & all.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply