Microsoft has released a whitepaper titled: "Running SQL Server with Hyper-V Dynamic Memory". The paper which contains 39 pages covers how to configure Dynamic Memory, introduced with the release of Windows Server 2008 R2 Service Pack 1, when running SQL Server workloads on top of Hyper-V.
For testing the scenarios a two-node IBM x3850 X5 Server with Windows Server 2008 R2 failover cluster was used, with 32 physical processor cores and 128 GB of RAM. Storage was provided by a dedicated HDS AMS2500 storage system.
The paper covers the following topics:
- Basics of Dynamic Memory and SQL Server memory management
- Core test scenario’s and workloads
- When will Dynamic Memory help SQL Server workload
- Impact of Virtual Memory removal on SQL Server workloads
- Considerations for planned and unplanned failover
- Memory Rebalancing Behavior, Impact on SQL Server Workload Performance, and Impact on the Host
- Impact of Increased Workload on Memory Consumption
Conclusions from the paper:
• Run SQL Server with the Lock pages in memory user right to provide better stability to the SQL Server workload during memory removal operations.
• Ensure that the total of Startup Memory settings for the virtual machines is configured to be lower than host’s physical memory so that all virtual machines can start in the event of an unplanned failover.
• When possible, use SQL Server memory management tools (sp_configure ‘max server memory’) to enable more orderly live migrations.
• Disabling NUMA spanning has some advantages to live migration scenarios and may offer some performance advantage due to locality of memory allocations (this is not illustrated or measured as part of this paper, however).
• Use memory weight to selectively adjust assigned memory to certain virtual machines dynamically.
• A recommended starting point for the memory buffer setting is 5 percent; you can adjust it based on your memory rebalancing needs.
• Many of the optimal settings and behaviors depend on your workload. You may need to perform further experiments and tests to determine what works best for any given deployment.