Hyper-V’s Dynamic Memory feature and SQL Server

Author: Joe
Date: 01/15/2018 12:58:00

While attempting to deploy a fresh copy of a Visual Studio database project to my SQL Server 2017 instance in Hyper-V VM, I ran into this error when attempting to create a column store index:

A timeout occurred while waiting for memory resources to execute the query in resource pool 'default'

After running across Joe Sack's post over at SQL Skills, I decided to try increasing my VM's RAM from 8 GB to 11.5 GB (the max I had available.)  I tried executing the CREATE COLUMNSTORE INDEX command again, and after 25 seconds, it timed out with the same error.

I checked the resource pool allocation requests, and the CREATE statement was trying to allocate 140 MB of RAM...which should've been available after the memory increase.

So, I checked Task Manager, and it said I only had 5.6 GB of RAM installed in the guest VM.  Say whaaaat?!?

Turns out, if your Hyper-V VM has the Dynamic Memory feature turned on, SQL Server won't request additional memory from the Hypervisor when it's close to the current "hardware max" value, which resulted in the timeouts I was seeing.  So, I shut down my VM, turned off the Dynamic Memory feature in the Memory section of my VM settings, restarted it, and the CREATE statement executed in a fraction of a second.

In my case, this is fine, since this VM is a development environment on my local machine, but for those who are using SQL Server on a Hyper-V VM, definitely review Microsoft's recommendations for using Dynamic Memory on the guest OS (the only article I could find.)