October 31, 2008 at 2:32 am
Hi,
We have a server configured as follows:
ESX 3.5 VM, 2 x vCPUs, 4GB RAM
Underlying hardware is dual Xeon E5440 processors & a NetApp SAN
Windows 2008 Server Datacenter Edition x64
4 x SQL instances;
- 3 x SQL 2008 Standard x64
- 1 x SQL 2005 Standrd x64
Out of our 4 SQL instances, one of the SQL 2008's is under constant high load (for us) - 20GB database, constant activity from 50 users + automated apps - while the other three are low load.
We started experiencing an issue where response times from the 3 low-load instances became extra-ordinarily long, and hence the applications relying on them also became slow.
Trouble-shooting the issue led me to RAM - as soon as set a minimum RAM allocation of 256MB on the 3 x low load instances, performance improved dramatically. Interestingly, the 3 x low load instances didn't start consuming significantly more RAM - definitely not 256MB each - but performance still improved.
From this I assume that when all instances are using purely dynamic memory allocation, the busy instance is hogging all the server's RAM, so that when the low load instances have to service requests they're having to fight for RAM, have to page data in from disk, etc, leading to the bad response times.
According to the MS documentation I've been able to find - e.g. http://msdn.microsoft.com/en-us/library/aa224754(SQL.80).aspx - multiple instances should sort out memory allocation between themselves fairly elegantly, so that they all get a reasonable slice for their respective workloads.
Clearly in our case this isn't happening - is there anything I'm missing? Or is the work-load differential just so high in our case that dynamic allocation just can't let the low load instances "keep up"?
Obviously we can stick with manually setting some lower / upper memory limits, but I'd prefer not to if possible - means we have to manually respond to changes in work-load on our instances, may be un-necessarily reserving more RAM for the low load instances than we need to, etc.
Any advice appreciated - thanks!
Cheers,
Matt
November 5, 2008 at 7:04 pm
No one?
November 6, 2008 at 2:02 pm
It has been *my* experience that upper bound memory (max) should be set in these cases.
In addition choosing CPUS per instance is also nice (if you can. This is usually employed for large number of CPUs)
* Noel
November 10, 2008 at 5:08 pm
noeld (11/6/2008)
It has been *my* experience that upper bound memory (max) should be set in these cases.In addition choosing CPUS per instance is also nice (if you can. This is usually employed for large number of CPUs)
OK, so you'd suggest putting an upper bound on memory for the busy instance, and leaving the other instances with no upper/lower bounds (in essence, leaving them to fight it out for the remaining memory)?
CPU affinity would be a no go, only 2 vCPUs in the VM.
Thanks for the advice.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply