John Baker
asked on
Virtual SQL Server Running Slow
We have a Windows Server 2012 R2 Server running Hyper-V. The virtual server is also Windows Server 2012 R2. The virtual server is running SQL Server 2012. I have allocated 16GB of RAM to the server and it has 12 virtual processors. The resource manager shows that it is not being worked hard but we have a program that uses SQL to pull reports and it is extremely slow doing it. Are there any thoughts?
Thanks for any help.
Thanks for any help.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Philip Elder,
The server has dual 8 core processors.
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.
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.
ASKER
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.
ASKER
ITSysTech,
I looked at memory and even doubled it to 32GB just to test and it made no difference.
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?
ASKER
Vitor,
I know that there are 8 but I am away from the server today and will get back on it on Monday.
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.