August 19, 2010 at 7:36 am
I am need of some advise. I have a 3 node cluster, 2 Active one Passive. Server boxes have 48GB RAM, each SQL instance Min/Max is set at 43008 MB to leave some for OS. According monitoring, OS memory usage is +95%. Is it time for more memory?
SQL Padre
aka Robert M Bishop
"Do or do not, there is no try" -- Yoda
August 19, 2010 at 7:56 am
One note about memory configuration that is often missed as follows;
"Note that the max server memory option only limits the size of the SQL Server buffer pool. The max server memory option does not limit a remaining unreserved memory area that SQL Server leaves for allocations of other components such as extended stored procedures, COM objects, non-shared DLLs, EXEs, and MAPI components. Because of the preceding allocations, it is normal for the SQL Server private bytes to exceed the max server memory configuration. For additional information about allocations from this unreserved memory area, click the article number below to view the article in the Microsoft Knowledge Base:
316749 (http://support.microsoft.com/kb/316749/EN-US/ ) PRB: There May Not Be Enough Virtual Memory with Large Number of Databases "
http://support.microsoft.com/kb/321363
So, if your OS is being starved for memory then you should either increase the memory available to it by adding memory to the box or by limiting SQL Server further. You will have to determine which is best for your scenario.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 19, 2010 at 8:15 am
I agree with David.
If cost is a consideration then just lower the amountn available to SQL (it is quite resiliant).
However a word on adding memory, we have a cluster with 128GB memory and 98GB has been made available to SQL and it used all of it!
SQL will use as much or as little as you give it. The choice is yours.
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 19, 2010 at 8:41 am
I understand that SQL will take all that she can from OS on memory, so what is an acceptable "range" of memory to designate to SQL. i would assume 95% OS memory used is not exactly "optimal", we left ~5GB of memory for OS I was hoping that would be enough. Do I leave a percentage or hard GB number for OS
SQL is using 100% of allocated memory right now, which I sort of expect.
SQL Padre
aka Robert M Bishop
"Do or do not, there is no try" -- Yoda
August 19, 2010 at 8:50 am
So if i understand you, your OS is using 95% of your 48GB or 95% of what it is allocated?
The thing to keep in mind is that at 80-90% memory useage (the whole 48GB) Windows will start paging out (unless like us you have disabled the option).
So you want to take say 80-90% of your memory and then divedie that into OS and SQL (assuming you want to avoid paging).
OS generally uses around 4-5GB so its strang if yours uses more. What else is it doing (ideally it should only be running SQL no IIS oor RS or anything like that).
Also keep in mind that from the task manager view, thats not all the memory usage. You have also got CLR, extended buffers and esp's. What you see in TM is just sqlserver.exe!
Adam Zacks-------------------------------------------Be Nice, Or Leave
August 19, 2010 at 9:05 am
Make sure you have Lock Pages in Memory set, and I typically try to leave 4gb free on systems up to 32gb, and 8gb free in the 48-128gb range, especially on server 2008. Anything less than 6-8gb available for the OS and it will be tight.
If you bumped those boxes up to 64gb and allocated 56gb for SQL, you'd be in good shape. I have a few clusters running with that config and I never have issues, and very little paging (if any).
August 19, 2010 at 12:04 pm
SQL_Padre (8/19/2010)
I am need of some advise. I have a 3 node cluster, 2 Active one Passive. Server boxes have 48GB RAM, each SQL instance Min/Max is set at 43008 MB to leave some for OS. According monitoring, OS memory usage is +95%. Is it time for more memory?
if both active nodes failover to the passive node (unlikely but it could happen) you could have memory issues. memory limits should take account of node limits and fail over resources!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 19, 2010 at 12:14 pm
Perry Whittle (8/19/2010)
SQL_Padre (8/19/2010)
I am need of some advise. I have a 3 node cluster, 2 Active one Passive. Server boxes have 48GB RAM, each SQL instance Min/Max is set at 43008 MB to leave some for OS. According monitoring, OS memory usage is +95%. Is it time for more memory?if both active nodes failover to the passive node (unlikely but it could happen) you could have memory issues. memory limits should take account of node limits and fail over resources!
I've heard this before but honestly if something catastrophic does happen and two nodes go offline, you should have some kind of alerting in place (scom or otherwise) to let you know so you can manually adjust max memory quickly.
There's no need to let 50% of your ram sit idle for the one in a billion chance that you lose just 2 nodes at the same time. And if that does happen, just log in and change some settings (you will obviously be very busy right about now anyway). Adjusting memory doesn't require a service restart.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply