Slow SQL performance after P to V migration

total123
total123 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
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/Storage

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

Author

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
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!

Author

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/Storage

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

Author

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/Storage

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

Author

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/Storage

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

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