SQL Server and dynamic memory with Hyper-V in Windows Server 2008 R2 sp1

  • Article

Running SQL Server in a virtual machine is a good thing for many reasons however there are two basic rules:

  • don’t over commit processors
  • use fixed or pass through disks (i.e. not dynamic one)

The guidance from the SQL CAT (SQL Server Customer Advisory Team) doesn’t extend to memory as up until now you can’t do much with memory in Hyper-V except allocate a fixed amount to each virtual machine which can’t be changed while its running.

However with the release of Windows Server 2008 R2 sp1 it is now possible to make use of dynamic memory.  This allows you to set a minimum amount of memory need to start a vm and then each vm can be accorded a number of properties to make use of any unused memory on a give physical server:

  • the maximum RAM
  • the percentage of memory that Hyper-V should try to reserve as a buffer (the free memory in the virtual machine)
  • the memory priority (form low to high)

The interesting thing here is that the amount used by the physical OS is controlled by Hyper-v unless you hack this in the registry (with the obvious word of caution implied in doing this).

You can still assign static memory to a virtual machine, so should you use dynamic memory with SQL Server?

The answer is probably yes with one caveat; the extra memory hyper-V will hand over once a virtual machine has started will appear to SQL Server as memory that has been hot added while the server is running.  Only certain version and editions of SQL Server have the capability to recognised hot-add memory -Only Enterprise and DataCenter editions for SQL Server 2005 and later.

If you use dynamic memory with other editions and versions the memory will still be there but SQL Server itself simply won’t recognise it.

For more on this check KB 956893