Chris Jones


Senior Solutions Engineering Architect with Nutanix, virtualisation guy, coder, automation junkie and fan of the Hawthorn Football Club.
Based in Melbourne, Australia.


Determining SQL Server Maximum Memory

Most of my time in the last couple of months at Nutanix has been consumed in testing and understanding Microsoft SQL Server running in different configurations on the Nutanix platform. A lot of these learnings have gone into the recently updated version of the Nutanix Best Practice Guide for SQL Server. One of the elements I was testing was the impact of setting the server property Maximum Server Memory and determining what the best value would be to use.

Before I go into the science behind it, I have come up with a little calculator to help determine this value for your specific configuration:

This calculator assumes the version of SQL Server is 2012 and onwards.

Cores
Total RAM (GB)
Architecture
Note: 32-bit versions are not available starting with SQL Server 2014 (12.x)
Other Apps (MB)

Recommended Maximum Server Memory value: 0 MB

Breakdown:

Memory 0 MB
Max Worker Threads 0
Thread Stack Size 0 MB
OS overhead 0 MB
Formula 0
0

The following outlines how this number is calulated and what it all means. Let's start with the forumla itself:

OS Reservation

Apart from the minimum and recommended requirements, Microsoft doesn't publish a fixed or predictable value for what Windows needs to be able to operate. Windows Server 2016 can run on a minimum of 512MB (core) or 2GB with the Desktop Experience. A recommended value to be able to do anything productive would be 4GB. Operating System memory space outside of SQL Server is required in order for the kernel to operate that supports all the host services.

In my testing across variuos sized SQL Server deployments ranging from 4GB servers to 512GB servers, I would recommend the Windows guest overhead (without SQL running or any other applications) to be 5% of total physical memory with a minimum of 1.5GB (1500MB) and a maximum of 4GB (4000MB).

In addition to the reserved OS space, additional memory space will need to be used outside of SQL for Windows to be able to scale CPU usage. As a conservative approach, allocate 250MB of additional RAM for every vCPU added to the virtual machine. This will be a value that is subtracted out of SQL server max memory.

Number of SQL Threads

Threads are an operating system feature that lets application logic be separated into several concurrent execution paths. This feature is useful when complex applications have many tasks that can be performed at the same time.

Threads allow complex applications to make more effective use of a CPU, even on computers that have a single CPU. With one CPU, only one thread can execute at a time. If one thread executes a long-running operation that does not use the CPU, such as a disk read or write, another one of the threads can execute until the first operation is completed. By being able to execute threads while other threads are waiting for an operation to be completed, an application can maximize its use of the CPU. This is especially true for multi-user, disk I/O intensive applications such as SQL Server.

From the Microsoft documentation (available here), there is a setting in SQL Server called max worker threads. This option configures the number of worker threads that are available to SQL Server processes. SQL Server uses the native thread services of the operating system so that one or more threads support each network that SQL Server supports simultaneously, another thread handles database checkpoints, and a pool of threads handles all users.

The default setting for max server threads is best for most systems. If you suspect that there is a performance problem, it is probably not the availability of worker threads. The cause is more likely something like I/O that is causing the worker threads to wait. It is best to find the root cause of a performance issue before you change the max worker threads setting.

The number of worker threads per SQL server is determined as follows:

  • For 32-bit up to 4 vCPUs, max worker threads equals 256.
  • For 32-bit with more than 4 vCPUs, max worker threads equals 256 + ( ( Number of Cores – 4 ) * 8 ).
  • For 64-bit up to 4 vCPUs, max worker threads equals 512.
  • For 64-bit with more than 4 vCPUs max worker threads equals 512 + ( ( Number of Cores – 4 ) * 16 ).

Thread Stack Size

Each new thread in Windows receives its own stack space consisting of both reserved and initially committed memory. The reserved memory size represents the total stack allocation in virtual memory. The stack size in SQL server depends on the platform architecture.

  • SQL Server x86 (32-bit) - Stack size is 512KB
  • SQL Server x64 (64-bit) - Stack size is 2MB.

With the max number of threads and the stack size per thread, we now need to ensure these are added to the maximum amount of memory SQL Server buffer pool can claim.