Virtual SQL Server Running Slow

If you suspect that the reports are causing issues you could also check "Recent Expensive Queries" and scroll down to see if any of them are extremely high.

Hello ITSystech,

What numbers would be high?  I do not have much experience with SQL.  The software company has configured the databases but they do not seem to want to help.

Philip Elder,

The server has dual 8 core processors.

My EE article explains what happens when we assign more vCPUs than physical cores on one CPU.

Suffice it to say, shut the VM down and back it off to four vCPUS and go from there.

Use PerfMon to monitor both host and in-guest resources while the reports are being run to pinpoint the bottleneck.

My suspicion is disk subsystem as that's usually where hidden problems lie. ResMon can be used to see what kind of latency there is on the VHDX files at the host level. Anything over 50ms to 100ms is getting into the catastrophic performance impact territory.

Let's start with with Processes. If you look at Memory Use (see photo) and then scroll down look for something that is in the thousands or more. We are trying to isolate the process that is causing your server to be slow assuming you are only using this server for SQL.

Philip, I have dropped the virtual processors down to 7 and it did not make a difference.  I have watched the ResMon and I am not seeing any latency in that range.

ITSysTech,

I looked at memory and even doubled it to 32GB just to test and it made no difference.

Check ResMon in-guest while the reports are running. Queue Depth is another indicator for I/O bottlenecks in disks.

How many databases do you have in the SQL Server instance and how big they are?

Vitor,

I know that there are 8 but I am away from the server today and will get back on it on Monday.

Inactive for 14 days.