One failover cluster has 2 nodes with 64GB RAM each: - Node A and Node B. Node A run 2 SQL instances (SQL1 and SQL2). Node B runs 1 SQL instance (SQL3).
SQL1 has a max server setting of 30,720MB and min server setting of 1024MB. SQL2 has a max server setting of 8192 MB and Min server setting of 1024 MB. SQL3 doesn’t have any min & max settings.
target server memory and
total server memory from perfmon, below is what I collected:-
SQL1:- Available MByte: 26GB, Target Server Memory: 30GB, Total Server Memory: 29.8GB SQL2:- Available MByte: 24GB, Target Server Memory: 8GB, Total Server Memory: 7GB SQL3:- Available MByte: 58GB, Target Server Memory: 54GB, Total Server Memory: 1GB
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.