Slow SQL performance after P to V migration

I have recently done a P to V migration on a SQL server for one of my customers. They are now saying they have slow read and write access to the SQL data base. They access it Via a client installed on the work station. It is running on Hyper-v (Gen 1 as it would not migrate to Gen 2)

I have already gone down the path of disabling the virtual machine Que on the network cards. The CPU and Ram usage never gets above around 25% usage.

The hyper-v server, is running two 10 core hyper threaded CPU, 96 gb of Ram, 10X 1.2TB 10K sas drives in Raid 10 and 10GB network. So the speed of the Server should not be an issue.

The VM is set with 8 cores and 16gb of ram and a single VHD file.

Am I missing somthing?
LVL 1
total123Asked:
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.

ste5anSenior DeveloperCommented:
hmm, a common problem is IO. So check the outstanding IO (disk queue depth) on the VM during "slow" sessions..

How many cores had the original machine, what kind of cores? Also hyper-threading maybe an issue. How many VM's are hosted on that machine?
Philip ElderTechnical Architect - HA/Compute/StorageCommented:
Back the VM off to four vCPUs and bump the vRAM up to 24GB.

I have two very thorough EE articles on all things Hyper-V:

Some Hyper-V Hardware and Software Best Practices
Practical Hyper-V Performance Expectations
total123Author Commented:
I have attached a screen shot of the disk queue length, via performance monitor

The physical server had 4 cores and 16gb of RAM/. I double checked the VM and its running 8 cores and 16gb of ram
The VM's running on the server are as follows:
  1. Cad Server: 4 cores 12 gb of ram
  2. Exchange server: 8 cores 30gb of ram
  3. File Server: 1core 8gb of ram
  4. SQL server: 8 cores 16gb of Ram
  5. Terminal server: 8 cores 20gb of ram  

Im wondering if there is a way to assign more disk resources to the VM? Not something I have ever looked into. Although it does not look like it has massivly High Disk usage
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

total123Author Commented:
Thanks Phillip Elder, I will try and Up the Ram usage. But it never goes above 6 gb usage.
Philip ElderTechnical Architect - HA/Compute/StorageCommented:
A VM's vCPU is a thread not a core.

Anything more than four vCPUs will be a detriment to performance unless tested to improve. The way the CPU pipeline works with the VM's threads (vCPUs) is also explained in my articles.

Back Exchange off to 3 vCPUs and RDS Session Host to 4 vCPUs.

Dollars to donuts performance will improve dramatically.
total123Author Commented:
Ok so do you think its worth knocking all cores on all VM's down to 4? As the exchange is set to 8 cores too.
Philip ElderTechnical Architect - HA/Compute/StorageCommented:
Correct. My thought is the CPU pipeline logic is having a hard time juggling those 8 threads in. All vCPU threads need to be processed in parallel. Meaning, they go through side by side. Thus, 8 threads side by side on a 10 core CPU doesn't have a lot of room to play with to get them in.

Bump the File Server VM up to two vCPUs while at it. If it experiences a runaway thread in the guest it will become unresponsive. I suggest always assigning a minimum of 2 vCPUs per VM and a maximum of 4 vCPUs with anything over that thoroughly tested.
total123Author Commented:
Ok Cool that makes sense so I will end up with:
  1. Cad Server: 4 cores 12 gb of ram
  2. Exchange server: 4 cores 30gb of ram
  3. File Server: 2 cores 8gb of ram
  4. SQL server: 4 cores 16gb of Ram
  5. Terminal server: 4 cores 20gb of ram  

So with the above solution I would use 18 of my 20 physical cores, leaving me with two spare. Meaning, if I want to add more VM's (which would cause me to exceed the physical cores) I would need to reduce the amount of cores on the existing VM's? (Am I getting this right?)
Philip ElderTechnical Architect - HA/Compute/StorageCommented:
Please read my articles for clarification on how the hypervisor and CPU pipeline work. ^^ Is not correct ^^

We can over-commit CPU resources based on a number of factors. Again, the articles explain it all as well as clarifies the nomenclature.
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
SQL

From novice to tech pro — start learning today.