November 6, 2013 at 8:54 pm
Hi,
I have a 2008 R2 64b server with 4 instances on it. One is using ALL the RAM and the other instances are using less than 100Mb and the performance for those apps is terrible. I have stopped the instance that's using all the RAM and waited to see if the other instances pick it up but they don't. I haven't changed any memory settings, but I've never seen one instance dominate so much. Is there a way I can balance this out without hurting the one hogging all the RAM.
Thanks
Kris
November 6, 2013 at 11:48 pm
Kris-155042 (11/6/2013)
Hi,I have a 2008 R2 64b server with 4 instances on it. One is using ALL the RAM and the other instances are using less than 100Mb and the performance for those apps is terrible. I have stopped the instance that's using all the RAM and waited to see if the other instances pick it up but they don't. I haven't changed any memory settings, but I've never seen one instance dominate so much. Is there a way I can balance this out without hurting the one hogging all the RAM.
Thanks
While you cannot share memory between instances, you can set a minimum amount of memory for SQL. Though, I must say, in my experience that does not always work. Have you looked at the memory DMV's to see just how much memory each database is using?
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
November 7, 2013 at 12:15 am
I've also in my experience not to do that, but I've never seen a SQL server give all the memory to one instance, particularly when they are all set up the same. Sorry I don't know what you mean by DMV.
Kris
November 7, 2013 at 12:21 am
This query will get you the information from the DMV (Dynamic Management View)
select db_name(database_id) as [Database Name], count(*) * 8/1024.0 as [Cached Size (MB)]
from sys.dm_os_buffer_descriptors
where database_id > 4 -- system databases
and database_id <> 32767 -- ResourceDB
group by db_name(database_id)
order by [Cached Size (MB)] desc option (recompile)
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
November 7, 2013 at 2:02 am
IMO you are probably best considering what databases are on which instance and giving a priority weighting to each. Then divide the available RAM up between these according to the weighting ratio then set MAX MEMORY on each instance. Make sure you leave enough RAM for the OS (and any other services or SQL features) to function.
If you find you don't have enough RAM to serve each instance efficiently this provides you with a case to upgrade the server as it has been over allocated.
November 7, 2013 at 4:11 am
If you don't set the max memory on each instance, they will fight for the memory resources and you could see a "winner" in that fight.
In general, if a set of databases can share resources, I'd suggest putting them all on a single instance. That way you can allocate all the physical resources to that instance and you're not duplicating aspects of SQL Server. If they can't share resources well, then it's probably better to put them on two separate boxes, not just two separate instances.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply