Microsoft SQL Server uses memory to improve performance by caching data, and it will use as much memory as it can based on how much it has been allocated. On a physical server, with a fixed amount of memory, that means making an initial estimate of how to divide the limited system memory between SQL, the operating system, and other applications on the server.
Determining how much memory to allocate to SQL without compromising the performance of other applications or the OS requires making an initial estimate, and then adjusting that based on observed performance metrics.
On a Virtual Machine (VM), since you can add more memory as needed, you can adjust not only the memory allocated to SQL, but also the overall memory on the VM. In order to calculate the initial memory allocation for a VM, take into account the following components:
Allocated VM Memory =
SQL Server Maximum + SQL ThreadStack + OS/Application + VM Overhead
|Download Achieving Peak SQL Performance - Learn how an improved monitoring strategy can help optimize database performance and reduce the amount of dedicated resources needed for database monitoring and management|
SQL Server Maximum
The recommendation for Miscrosoft SQL 2016 Standard and Enterprise editions is that 4 GB memory be available for each SQL instance, with increases in memory as the SQL databases increase in size. To determine if more memory is needed for SQL, monitor the following Performance Metrics:
- Buffer Manager/Buffer Node: Page Life Expectancy (PLE)
PLE indicates the number of seconds a page in memory has to live if it isn’t touched - if there is memory pressure, this value will decrease. Monitor for a persistent drop below the baseline value.
- Buffer Manager: Buffer Cache Hit Ratio
The Buffer Cache Hit Ratio is the percentage of pages that were found in the buffer pool rather than needing to be read in from disk - this should be > 90%. If the ratio drops below this value it could indicate memory pressure, but it might also indicate badly constructed tables and/or indexes.
- Memory Manager: Memory Grants Pending
Ideally there should be no processes waiting for memory and this should = 0. More memory is needed if this value is >=1.
- Memory Manager: Target Server Memory vs Total Server Memory
The Target Server memory is the amount of memory that SQL wants to consume - Total Server memory is what it is actually consuming. More memory is needed if Target Server memory is greater than Total Server memory.
SQL ThreadStack Memory
SQL threads are used to handle query requests - the maximum number of threads depends on the number of CPUs and whether the OS is 32 bit or 64 bit. See Configure the max worker threads Server Configuration Option for the default maximum number of threads per CPU.
Each thread has memory associated with it. The thread memory is based on processor architecture:
The ThreadStack memory is calculated as:
Maximum # threads * Memory/thread
For example, for 8 x64 processors, the default maximum number of threads is 576 (see https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option), so the ThreadStack memory would be:
576 threads * 2 MB = 1152 MB = 1.125 GB
OS and Application Memory
The exact amount of memory required for satisfactory OS and application performance is dependent on the server’s function and load. The challenge when working with a finite amount of memory on physical servers, is to balance providing enough memory for SQL without unduly limiting the memory available for the OS and applications. For VMs, allocated memory can be increased as long as the active memory across all running VMs does not approach host memory capacity.
In both the physical server and VM cases, monitoring overall memory use will determine if the OS and applications require more memory. Watch the following counters and monitor them for deviations from baseline values:
- Memory: Available Bytes
- Memory: Pages/Sec
- Memory: Page Faults/sec
- Process: Page Faults/sec
For a SQL Server on a VM, you will also need to allocate memory for the overhead of powering on the VM itself. In VMware, the exact amount of overhead used by the VM will vary after it starts up, but the initial memory used at startup is a good estimate. Architecting Microsoft SQL Server on VMware vSphere has the following sample values:
|Memory (MB)||1 VCPU||2 VCPUs||4 VCPUs||8 VCPUs|
|256||20.29 MB||24.28 MB||32.23 MB||48.16 MB|
|1024||25.90 MB||29.91 MB||37.86 MB||53.82 MB|
|4096||48.64 MB||52.72 MB||60.67 MB||76.78 MB|
|16384||139.62 MB||143.98 MB||151.93 MB||168.60 MB|
The memory overhead for VMs hosted on Hyper-V is:
The memory cost associated with running a guest operating system on a Hyper-V virtual machine was observed to be approximately 300 MB for the hypervisor, plus 32 MB for the first GB of RAM allocated to each virtual machine, plus another 8 MB for every additional GB of RAM allocated to each virtual machine.
SQL Virtualization Considerations
In addition to allocating enough memory, there are considerations specific to running a SQL server in a VM:
- Memory Overcommitment
Virtualization allows you to assign more memory to VMs than actually exists on the servers hosting them. This is possible because VMs generally do not use all the memory they are assigned at the same time, allowing virtualization to provide only the memory that the VM has in use rather than the memory it has been allocated. The assigned memory that is not actively used is then available for additional VMs.
Overallocation breaks down when the memory in use approaches the physical limit on the host, leading to severe performance degradation on VMs. Since SQL’s performance is highly dependent on memory, it is strongly recommended that memory not be overcommitted in an environment running SQL server VMs.
- Minimum Memory
VMs can have minimum memory settings which set aside resources specifically for the VM - for example, the Minimum RAM setting under Dynamic Memory for Hyper-V, or the Reservation for VMware. Setting a minimum memory value has the advantage of ensuring that your SQL Server VM will not have memory taken away from it. The disadvantage of setting a memory minimum is that if the VM needs to be moved to a different host, that host must have at least the minimum free memory before the VM can be powered on.
- Memory prioritization
In addition to prioritizing memory for VMs through Reservations or Minimum RAM, VMware and Hyper-V can prioritize memory on a per VM level. Memory prioritization is done using shares in VMware, and using memory weight in Hyper-V. While memory prioritization is only considered if host memory is overcommitted, hardware failures can remove resources in a cluster that could lead to resource shortages.
Microsoft SQL Server performance depends heavily on having enough memory to cache data. Whether determining how much memory to allocate to SQL on a VM, or determining how to divide limited memory between SQL and OS and applications on a physical server, be prepared to monitor memory performance metrics for both SQL and the the OS and adjust memory allocation as needed to optimize performance.