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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ITSysTechSenior 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)?
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.
ste5anSenior 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?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Vitor MontalvãoMSSQL 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?
Scott PletcherSenior 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.
Philip ElderTechnical 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
John BakerOwnerAuthor Commented:
Philip Elder,

The server has dual 8 core processors.
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.
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.

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

I looked at memory and even doubled it to 32GB just to test and it made no difference.
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
How many databases do you have in the SQL Server instance and how big they are?
John BakerOwnerAuthor Commented:

I know that there are 8 but I am away from the server today and will get back on it on Monday.
ITSysTechSenior Systems AdministratorCommented:
Inactive for 14 days.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.