We have a 2003 cluster with 32GB of memory.
It hosts two SQL 2005 DB instances, one Reporting instance and runs SSIS packages throughout the day.
The main instance is set to 22GB of memory, the second is for SharePoint and has been set to 4GB and limited to 2 processors - everyone is very suspicious of sharepoint, so it's been locked down.
The application pool for Reporting Services is limited to 6GB - which could probably be reduced. Reporting services will be moved to it's own server soon, so we'll be out of this situation.
By setting memory this way, we were hoping to not have applications constantly fighting for memory.
We need to set up a 2008 instance now for a few web applications which will be moved off the 2005 instance.
We're not seeing much paging on the server - it normally runs around 0 to 8 pages/sec and hops up to a few hundred here and there.
Both instances have the same values for total/target SQL memory. I was hoping to see the target memory value lower for the main instance and I would reduce the memory to that level to make room for the new instance. I have seen the target memory lower than the total by a few hundred megabytes in the past.
What would you folks recommend doing to make room for this new instance while keeping the other instances happy? Maybe go dynamic with the main instance? Reduce the Max of the main instance slowly until paging or some other counters go up?
It sounds like you're doing OK.
Yes. I'd drop the RAM allocated to the main instance until you start to see cache hits drop below 99% (a flawed metric I know, but can be used with care) and look for increases in disk IO rates (disk queue length, average response times etc) as well as the good old "user test" - Does it seem to run slower to the user or do they not notice?
Personally, I like my database servers to ONLY have SQL Server installed on them. I seperate reporting server and SharePoint (any IIS service) onto their own server.
You may want to investigate virtualisation...