Max and Min Memory Setting

memorysql-server-2008windows-server-2008-r2

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.

Best Answer

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.

Related Solutions

Sql-server – Memory settings for multi-instance failover clustering

Just did this for a client. They had 16 Gigs on each node, with each instance using 8 Gigs. One instance was starving for more RAM, so we upped it to 12, with alerting setup to watch for a SQL restart and send out a message. In the event of a failover due to hardware failing they'd manually lower it until the hardware was fixed.

SQL Server Memory Usage doesn’t tally

115982 is a very similar question

Is this a 64bit server - do you have the lock pages in memory local policy enabled?

the most likely reason is that this SQL Server 64bit instance is using “locked pages” as I’ve described in an earlier question. Notice the name on the Task Manager column is called Memory (Private Working Set). Remember we also said that if SQL Server 64bit instances use “locked pages” this memory would not be part of the working set (because remember AWE APIs are used on 64bit to “lock” pages and that memory is not part of the working set). So since the locked pages are not part of the working set, they won’t appear in this column in Task Manager. On the Task Manager for Windows Server 2003, this column is called “Mem Usage” but it also reflects the working set of the process.

Here is an article that explains it in depth

You can also view the counters in SQL

SELECT
    object_name
   ,Counter_name
   ,cntr_value
   ,ROUND(( cntr_value * 8192.0 ) / 1048576, 0) AS cntr_value_MB
FROM
    sys.dm_os_performance_counters
WHERE
    object_Name LIKE '%Buffer Manager%'
    AND RTRIM(counter_name) IN ( 'Free pages', 'Total pages',
                                 'Database pages' ) 
UNION SELECT
    object_name
   ,Counter_name
   ,cntr_value
   ,ROUND(( cntr_value / 1024 ), 0) AS cntr_value_MB
FROM
    sys.dm_os_performance_counters
WHERE
    counter_name IN ( 'Target Server Memory (KB)',
                      'Total Server Memory (KB)' )

Related Topic