SQL Server and Dynamic Memory

Since our move to Hyper-V and the addition of Service Pack 1 for Windows Server 2008r2, the addition of Dynamic memory has helped us vastly manage the resources within our virtual servers much better.  For those who don't know, Dynamic Memory is where the memory utilised in virtual environments can be changed dynamically while the virtual machine is running.  For this to work the OS needs to be aware and it is something that SP1 does nicely for us.

I came across something odd a few weeks ago after deploying a new virtual machine running SQL Server 2008 Standard Edition.  The server had plenty of resources as the OS was able to utilise much more memory as and when it was needed.  The problem was that the SQL Server would fall over without notice.  After reading through the logs, it soon became apparent that the SQL Server would continuously keep running out of memory.  The virtual machine has its settings set to a minimum of 512mb of memory, which will balloon when it is required.  The problem is that the standard edition of SQL Server uses a fixed amount of memory from the OS when it's running and it's not able to ask for more resources.  The only editions that can utilise Hot Memory are the Enterprise and Datacenter editions.

So the moral of the story is that the next time you install your SQL Server and you are running on the Hyper-V platform, it always almost certainly will benefit you to install the Enterprise edition so that you don't need to set your virtual machine to use a much higher fixed memory that the other virtual machines running within that pool can't use.