The issue at hand is the difference between how SQL Server and SQL Server Express allocate memory.
SQL Server consumes memory and does not relinquish it unless a request for memory is made. Because of this, the server will allocate all of the memory SQL Server is requesting (which grabs and holds dynamic memory unless Hyper-V requests memory back.
SQL Server Express immediately lets go of memory (within a few minutes) to try to be nice to other processes – designed for multitasking, not dedicated servers. As a result of this, when using dynamic memory it has to request memory allocation from the OS which has to request memory from the Hypervisor. While this is occurring there is a wait while it is trying to obtain memory to run the query.
The solution:
1.) Set the startup ram and Minimum ram for Dynamic memory to be equal to the maximum Server memory set in the SQL instance.
a. So, if the SQL instance only requires 2 GB of memory, the Hypervisor should set startup ram and minimum RAM to be 2048 MB.
b. The memory properties of the MS SQL Express instance should be set to a Maximum Server Memory of 2048.
*Note: If there are other things running on the system, the Startup and Minimum RAM in the Hypervisor should be slightly higher. The key point is that the Dynamic memory of the Hypervisor should not be less than what is allocated to the SQL Server Express.
Comments