Virtual SQL Server Running Slow

John Baker
John Baker used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ITSysTechSenior Systems Administrator
Commented:
Could you connect to your SQL instance by using Microsoft SQL Management Studio and right click on your instance name and chose Activity Monitor? Then go to DataFile I/O is there anything running really high (it might take a minute to process)?
ITSysTechSenior Systems Administrator

Commented:
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.
ste5anSenior Developer
Commented:
hmm, too many processors for that amount memory or vice versa..

but you need to dig into it. Is some resource blocking execution? How big is the entire database? Does your reporting require cold data? Do you have proper indices?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
SQL Server usually doesn't require much CPU. What it really important for SQL Server is memory. Can you provide more memory to see if the performance will be better?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
but we have a program that uses SQL to pull reports and it is extremely slow doing it.  Are there any thoughts?

1) You need to look at the specifics of that program, to make sure it's not doing things that are obviously bad, such as non-sargable WHERE clauses, accidental cartesian joins, etc..

2) Look at the I/O and index stats on the instance.  Also, be sure there's not a physical I/O issue before you spend too much time looking at other things.
Technical Architect - HA/Compute/Storage
Commented:
How many physical cores on the host's CPU(s)?

To the physical host's CPU subsystem one vCPU = one Thread. One Thread can be processed by one physical Core. I don't include Hyper-Threading as it has no real impact on performance.

Our rule of thumb is # physical Cores -1 for vCPU assignment. Having that count does not mean that maximum performance would be obtained. The CPU pipeline needs to juggle those threads as they need to be processed in parallel.

I have an EE article that explains a lot: Some Hyper-V Hardware and Software Best Practices.
John BakerOwner

Author

Commented:
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.
John BakerOwner

Author

Commented:
Philip Elder,

The server has dual 8 core processors.
Philip ElderTechnical Architect - HA/Compute/Storage

Commented:
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.
ITSysTechSenior Systems Administrator

Commented:
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.

1.JPG
John BakerOwner

Author

Commented:
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.
John BakerOwner

Author

Commented:
ITSysTech,

I looked at memory and even doubled it to 32GB just to test and it made no difference.
Philip ElderTechnical Architect - HA/Compute/Storage

Commented:
Check ResMon in-guest while the reports are running. Queue Depth is another indicator for I/O bottlenecks in disks.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

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

Author

Commented:
Vitor,

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

Commented:
Inactive for 14 days.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial