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.)