By Lisa Jackson-Neblett (PFE) – firstname.lastname@example.org
There are many factors to consider when running SQL Server in a Hyper-V Virtual Machine, guest. Being proactive with the Virtual Machines memory settings can assist you
in avoiding several memory issues that could lead to performance degradation. Understanding how memory can impact SQL Server’s buffer pool is important. Dynamic changes to the size of the buffer
pool can negatively impact performance of SQL Server. In the event the buffer pool size changes due to external factors like memory being removed from the virtual machine it could
impact SQL Server’s performance. The performance may be decreased by the hypervisor requesting memory from SQL Server. Thus, SQL Server would perform
more physical disk reads vs. logical disk reads because the data is no longer in memory. SQL Server also uses memory for a plethora of other activities like storing cached plans. Therefore,
optimizing memory in SQL Server Virtual Machine is important to achieve optimal performance.
Hyper-V has two main memory management settings for virtual machines. The first is static memory. When static memory is used the virtual machine gets a fixed amount of memory.
If the virtual machine uses static memory, the virtual machine memory should remain constant.
The second memory setting is dynamic memory. Dynamic memory is used to allow the host to reclaim and redistribute memory to the virtual machines as needed. There are three main dynamic memory settings,
Startup RAM, Maximum RAM and Memory Buffer. Startup RAM is the amount the virtual machine is allocated on startup. If there is not enough memory allocated or available on the host server the virtual machine will fail to
start. I use the following formula for obtaining a starting point for configuring the Startup RAM memory.
Startup RAM = OS needs (typically 1-2GB but can vary) + min_server_memory (SQL Server Setting)
If the min_server_memory setting in SQL Server is set to its default of 0 you will want to have a minimum Startup RAM => OS needs (typically 1-2GB but can vary) + at least 1 GB which is the minimum requirement
for SQL Server. Theoretically you could run it with less but will most likely impact performance if not enough memory is allocated to SQL Server.
Maximum RAM is the maximum amount of RAM that will be available to the virtual machine. I use the following formula as a starting point for configuring the Maximum RAM memory.
Maximum RAM => OS needs (typically 1-2GB but can vary) + max_server_memory (SQL Server Setting) + + memory requirements for other applications running in the virtual machine + 5% (Memory Buffer allowance).
The last setting for dynamic memory is the Memory buffer. The memory buffer is used by Hyper-V to specify how much extra memory is needed for cache files to obtain peak performance. Since SQL Server already manages its own memory and it will use all it is allowed to you will want to make this setting as low as possible which is 5%.
Once you have determined how you are going to configure your memory settings it will be important to check performance counters on the host and virtual machine to see if the memory settings are good or if they need to be “tweaked” to obtain optimal performance. Other performance metrics should be measured from within SQL Server as well to make sure you have the optimal settings.