4

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.

After tracking Available mbyte, 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.

db7
  • 135
  • 4

3 Answers3

1

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.

BrandonB
  • 161
  • 1
  • 10
  • That's correct. There's no load balancing in a SQL cluster outside of manually setting up what runs on each node. – Shane May 17 '11 at 14:02
1

There is not a lot of imformation to go on here, like, what are the SQL databases doing and why the cluster is active / active.

I can only assume from the details above that:

  • Node 1 is running two "low priority" databases (SQL1/SQL2)
  • Node 2 is running a more important database (SQL3) that requires dedicated CPU / RAM and you want to have a failover for that (to node 1)
  • You have shared storage that is not currently disk IO bound (i.e. the CPU being used is not because it is waiting for buffers to be filled)
  • You've done all the usual performance bottleneck checks

On a failed over system, you'd only have 18GB of RAM for SQL3. How would SQL3 run on less than 1/3rd of the usual RAM? The 'target size' suggests that it likes lots of RAM.

Guy
  • 2,668
  • 2
  • 20
  • 24
1

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

Jim B
  • 24,081
  • 4
  • 36
  • 60