A hosting company called me to investigate an issue with their SQL Servers instances, one client was having issues with the application whilst another client using the same application on another instance with more active users had no problems. The quick fix was to stop and start the SQL Server services. It appeared as though the application was not the culprit.

Upon investigation I found that:

1. The plan cache was basically empty.

2. Thee forced grants reported in the DMV sys.dm_exec_query_resource_semaphores, indicating memory pressure had affected query runtimes.

3. No missing index recommendations in the DMV’s.

4. Page Life Expectancy was low – although not a great indicator, it is still useful.

My conclusion was that it was starved of memory.

The underlying physical server has 64GB of which 40GB was allocated to 5 VM hosts. The VM for the SQL Servers had 6GB and on this VM there were 4 instances of SQL Server all with the memory min/max MB set to 0/2147483647.

In this scenario you are virtualising at the SQL instance layer on top of a virtualised environment. I am surprised that it ran, because the OS would have had hardly any memory. The one instance that ran Ok was the one that got in first and grabbed all the memory, the rest just picked up the scraps. They increased the memory to this VM to 10GB, we set the two small instances min/max MB to 128/256 and the two larger instances to 2048/4096. This way we had separate memory for each instance and at worst the OS would still have 1.5GB (not enough - but better). It just shows that you have to change the defaults and understand the whole environment. At the moment things seem to be running a lot better.