How Much Memory Does Microsoft’s SQL Server Need?

July 11, 2017 | Ken Leoni

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 RatioThe 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 PendingIdeally 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 MemoryThe 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:

x86	1 MB
x64	2 MB
IA64	4 MB

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

 

VM Overhead

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.

 

Conclusion

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.

 

Optimize SQL Server Performance

Learn how an improved SQL Server monitoring strategy can help optimize database performance.

Reduce the amount of dedicated resources needed for database monitoring and management.

Does Your SQL Server Have Enough Resource?