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

Dear Friends,

Please do bear with me as I am going to cover this topic in 3 parts.

Part 1 – To give a background on “why was this exercise initiated, to collect performance counter values and then to study and analyse the memory pressure on the current SQL instance(s)”

Part 2 – How we went about setting up the performance counters to be collected for a duration of 18 hours and which counters were used.

Part 3 – Analysing the values captured and then based on the results, arriving to a conclusion on the new “minimum and maximum memory settings” to be proposed for the SQL instance(s) concerned.

Part 1:

Our department’s infrastructure team has decided to upgrade the specs of systems hosting the SQL instances and web applications on the TESTING and DEVELOPMENT environments. This is as part of their objective to improve performance and resilience of some our critical web applications on the TESTING and DEVELOPMENT environments.

The decision was to implement a new 2 node active-active cluster for the SQL instance(s) that host the database(s) for some of the critical web applications on the TESTING environment. The new infrastructure proposed for hosting the identified existing SQL instances were 2 new servers [2x HP XXXXX XX blades, 24GB RAM, 2x Intel XXXXX 2.4 GHz 6-core processors, dual port 4Gb Xxxxxx HBA, Quad Port 1Gb NIC, 512MB Flash Backed Cache].

We had already identified 5 SQL instances on the TESTING environment that needed to be migrated on to these new infrastructure.

As part of the design stage, our DBA team was entrusted with the responsibility of analysing &recommending:

1. The SQL instance layout on the 2 node active-active cluster, meaning which SQL instance would be located on which node.
2. Minimum & Maximum memory allocation for each of the SQL instance on the 2 node active-active cluster.

A simple straight forward request, but this meant:

1. Collecting information on the current memory usage of the SQL Server instance(s) under the existing infrastructure to analyse the memory pressure on each SQL instance.
2. Analysing whether a particular instance is “under how much memory pressure” and “how much more memory is required to be allocated to eliminate the issue”
3. Based on proposed memory allocation determine which SQL instance will be located on which node of the cluster.

Note:

1. As part of performance counter collection, we decided to intentionally leave out I/O related counters because as part of the new infrastructure offering, the new nodes would be given vRaid 5 and vRaid 10 disk(s) for the DTC, Quorum, data and log files.
2. Since each single node of the cluster [it is a 2 node active-active cluster] has 24 GB RAM, we took a stand that the “total sum of Maximum memory” allocated to SQL instance(s) located “on a single node” should not exceed 10 GB.
3. This is to ensure that “if all the 5 SQL instances” were to be “active on a single node”, due to a failover either because of a node failure / restart, the total memory allocated to all 5 SQL instances would not exceed 20 GB, thereby leaving 4 GB of memory for other OS and miscellaneous processes.