September 3, 2010 at 4:03 pm
I need some validation on something I just told my boss.
On a single server with 32GB RAM, we have 4 instances. Someone in their ever present wisdom, and without my knowledge, set all four instances to 21GB.
One of the instances started to flake out so they called me. My suggestion was to leave 2GB free for the OS and measure the rest out so that none of the instances would have to contend for RAM.
Any additional input?
Thanks!
September 3, 2010 at 4:37 pm
x64 or x86?
I would leave 2GB for the OS, but monitor and check the pagefile for lots of activity. Leaving3-4GB can't hurt.
For SQL, you'll have to experiment with each instance. Easy to say they call get 7GB, but that might not be right.
Slightly old, but some advice here: http://itknowledgeexchange.techtarget.com/sql-server/single-instance-vs-multiple-instances/
September 8, 2010 at 10:41 am
Thanks! I figured, and told them as much. Was totally shocked that these guys would risk the contention.
It all seems to be working better now. Changed the two highest activity instances to 10GB and the two lower ones to 5GB. Left 2Gb for the OS.
September 8, 2010 at 11:59 am
Page Life Expectancy is a good measure to see if instances need more or less memory, btw. I have a bunch of multi-instance boxes and the difference between <5min PLE and >12hr PLE was going from 4 to 6 gigs of ram for that particular instance. Run a report every 10 minutes or so of the PLE and dump it to a table with timestamps and instance names so you can see what the trend is, and then adjust from there.
For PLE in Seconds (2005/2008):
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
AND object_name like '%Buffer Manager%'
September 8, 2010 at 3:01 pm
jerry-621596 (9/8/2010)
Thanks! I figured, and told them as much. Was totally shocked that these guys would risk the contention.It all seems to be working better now. Changed the two highest activity instances to 10GB and the two lower ones to 5GB. Left 2Gb for the OS.
With 32GB of memory available, you would be much better off leaving 4GB for the OS. At 2GB only the OS can become starved and then all instances would suffer.
Also check for additional services that could take up memory. For example, integration services (SSIS) packages, reporting services (SSRS), third-party utilities, etc...
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply