• Adam Thurgar

Multiple instances


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.


6 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t