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.
John BakerOwnerAsked:
Who is Participating?
 
Philip ElderConnect With a Mentor Technical Architect - HA/Compute/StorageCommented:
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.
0
 
ITSysTechConnect With a Mentor Senior Systems AdministratorCommented:
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)?
0
 
ITSysTechSenior Systems AdministratorCommented:
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.
0
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

 
ste5anConnect With a Mentor Senior DeveloperCommented:
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?
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
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?
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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.
0
 
John BakerOwnerAuthor 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.
0
 
John BakerOwnerAuthor Commented:
Philip Elder,

The server has dual 8 core processors.
0
 
Philip ElderTechnical Architect - HA/Compute/StorageCommented:
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.
0
 
ITSysTechSenior Systems AdministratorCommented:
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
0
 
John BakerOwnerAuthor 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.
0
 
John BakerOwnerAuthor Commented:
ITSysTech,

I looked at memory and even doubled it to 32GB just to test and it made no difference.
0
 
Philip ElderTechnical Architect - HA/Compute/StorageCommented:
Check ResMon in-guest while the reports are running. Queue Depth is another indicator for I/O bottlenecks in disks.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
How many databases do you have in the SQL Server instance and how big they are?
0
 
John BakerOwnerAuthor Commented:
Vitor,

I know that there are 8 but I am away from the server today and will get back on it on Monday.
0
 
ITSysTechSenior Systems AdministratorCommented:
Inactive for 14 days.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.