SQL Server: Performance counters used to study and analyze the current memory usage of SQL instance(s) to analyze memory pressure - Part 3

In this section I am going to cover, as to how we went about analyzing the performance counter values captured and then based on the results, arriving to conclusions on the new “minimum and maximum memory settings” to be proposed for the 5 SQL instance(s) concerned.

Anyone interested in reading up the background on this, can find Part 1 - here & Part 2 – here.

We collected data on 5 SQL instances.

Instance 1:

Average PLE value:

1. Way below 300 for major part of the day, averaging around 75 for extended period during peak hours. ¼ of the recommended value.
2. Overall average was at 150. Half the recommend value.
3. Indicating tremendous memory pressure.

 Average of Total Target Memory (KB):  

1. Remained constantly at 2048 MB throughout the monitoring period.
2. Target memory averaging = to the max memory setting on SQL instance right from the onset of monitoring.

 Average of Total Server Memory (KB):

1. Remained constantly at 2048 MB throughout the monitoring period.
2. Total = Target, with memory right up the maximum allocated level throughout the monitoring period + coupled with PLE value, strongly indicates tremendous memory pressure.

 Outcome:

1. Based on the above readings we decided to propose a maximum memory setting of 8192 MB, i.e. 2048 * 4 = 8192 MB.
2. PLE value was also a deciding factor on implementing 2048 * 4, i.e. 4 times the value of Total Server Memory.

Instance 2:

Average PLE value:

1. Way above 300 for major part of the day, sometimes even more that 1000 - 2000 for extended period during peak hours.
2. Only 3 instances each of 15 minute durations, where the average fell below 300 .
3. Indicating enough memory for major part of the day, though we needed more assurance on the above 3 instances where the PLE fell below 300.

 Average of Total Target Memory (KB):  

1. Averaged 1.6 GB to 1.7 GB throughout the monitoring period.
2. Average of Target memory closing in on to the RAM availability on system = 2 GB.

 Average of Total Server Memory (KB):

1. Remained below Target for major part(s) throughout the monitoring period.
2. Though it was below target, the difference was not above 100 MB for majority of the monitoring period. A crucial reading.
3. Averaged 1.5 GB to 1.6 GB throughout the monitoring period.
4. It equalled Target for those periods, where the PLE value(s) had dipped below 300.
5. This indicated there had been instances when SQL was under memory pressure, though the pressure disappeared subsequently.  

 Outcome:

1. Though not required, an increase in memory could well help the SQL instance in removing the minor, memory pressures issues seen above.
2. Based on the above readings we decided to propose a maximum memory setting of 3072 MB, i.e. double the average reading of Total Server Memory.

Instance 3:

 Average PLE value:

1. Way below 300 for major part of the day, averaging around 100 for extended period during peak hours. 1/3 of the recommended value.
2. Overall average was at 200.
3. Indicating memory pressure.

 Average of Total Target Memory (KB):  

1. Averaged 2.2 GB to 2.4 GB throughout the monitoring period.
2. Average of Target memory closing in on to the RAM availability on system = 2.5 GB.

 Average of Total Server Memory (KB):

1. Total was equally Target through those period where there was a tremendous dip in PLE value.
2. During times, though not many a times, when Total was < Target, the difference was not above 40 MB.
3. Total was averaging 2.1 GB
4. This indicated the server was under memory pressure majority of the time, though not always.

 Outcome:

1. Based on the above readings we decided to propose a maximum memory setting of 6144 MB, i.e. 2048 * 3 = 6144 MB.
2. PLE value was also a deciding factor on implementing 2048 * 3, i.e. nearly 3 times the value of Total Server Memory.

 Instance 4:

 Average PLE value:

1. Way above throughout the day.
2. Overall average value of PLE was at 9900, many a times even above 11000. Way – way high above the required value of 300.
3. No doubt, what so ever. SQL instance over here has enough / lots of memory.

 Average of Total Target Memory (KB):  

1. Average of target was at 950 MB.
2. Max value of target was 1.2 GB.

 Average of Total Server Memory (KB):

1. Throughout the day total, we did not have a single instance of total equalling target .
2. Average of total for the day was at 800 MB

 Outcome:

1. Based on the above readings we decided to propose a maximum memory setting of 1024 MB.
2. The high PLE value gave us enough confidence that the SQL instance can perform at an optimum level at 1024 MB.

So the final proposed SQL instance layout with max memory allocation on the 2 node active-active cluster was as below: