Please be advised that this is active/active failover cluster. My question is How do I set the max and min memory? This is 64-bit Win2k8 R2 server with SQL 2008 R2 instances. I have calculated and reached at 7GB to leave for OS, worker threads, multi page and other app. But, I need to figure out how to balance the SQL instances min and max memory setting. How do I know the memory that is needed for each instance? What is the best practice for setting the min? Please help.

This max and min was already setup on the instances by someone and I figured this won't be accurate as HA scenario. So, I need to adjust it and need your input on how I could adjust it. Or, how i could determine which instance need this much RAM or so. I guess in short how do I determine memory utilization of each instance.

3 Answers
3

You would to set the minimum to be able to accommodate complete failover otherwise your HA
scenario won't work. I don't know to much about SQL but, I am pretty sure MS only supports active/active in regards to SQL for HA not load sharing.

the min and max server memory options tell sql server how much buffer ram should be allocated. I have this in my notes on building sql clusters which apparantly comes from MSDN here

examine the SQLServer:Buffer Manager performance object while under a load, and note the current values of the Stolen pages and Reserved pages counters. These counters report memory as the number of 8K pages. Max server memory should be set above the sum of these two values to avoid out-of-memory errors. An approximate value for the lowest reasonable max server memory setting (in MB) is ([Stolen pages] + [Reserved pages])/ 100.

The tricky bit comes when you find you don't have enough ram on 1 node for everyone to be happy at the min server memory.. You now have to choose your least favorite instance and make it suffer, or share the pain and pick a few to suffer.

'min server memory' defines the limit at which SQL will stop releasing memory dynamically. This setting is usually unnecessary. It does not guarantee that SQL Server will be allocated at least this much memory. When SQL Server starts, it commits just as much memory as necessary, even if that is less than the 'min server memory' limit. As more data pages and query plans are cached, the memory that SQL commits increases. When committed memory increases above the 'min server memory' limit SQL can free pages (down to that limit), as necessary, to keep the minimum free physical memory about 10 MB