With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.
Course Of The Month
6 days, 9 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL 2005 High CPU on Server 2008 VM
Posted on 2012-12-28
Aloha,
I have the current setup:
Dell R420 w/ PERC H310 controller
32 gb RAM
1.8tb local storage, RAID 10 (4x7.2K SAS drives)
VMWare Essentials 5
There are only two VMs on this server. Both are Server 2008 R2.
I am having a problem with one of the VMs. It is running SQL 2005 SP4. Whenever I launch the application (Microsoft Dynamics RMS 2.0) that accesses the database in SQL 2005, CPU usage hits 100% and the application often freezes and times out. In the times that the app does not time out, I can access the data through the application without any problems (with the exception of running reports, which are slow and tax the CPU).
The VM is configured for 16gb RAM and 2 socket x 2 core processors (4 total processors).
The machine I migrated this database from is six years old, with 4 gigs of RAM, RAID 1 (mirrored drives) and a single processor. Performance is great :)
Can someone point out where I have a configuration problem/issue? I have ran esxtop and DAVG/rd/wr seldom exceed 15ms (same with GAVG/rd/wr). I initially thought it was a performance issue due to the 7.2K drives, but that doesn't seem to be the problem.
Any advice would be greatly appreciated. Apologies if I don't respond until Monday, it's almost the weekend (thankfully)
Many Thanks,
Tony
I have the current setup:
Dell R420 w/ PERC H310 controller
32 gb RAM
1.8tb local storage, RAID 10 (4x7.2K SAS drives)
VMWare Essentials 5
There are only two VMs on this server. Both are Server 2008 R2.
I am having a problem with one of the VMs. It is running SQL 2005 SP4. Whenever I launch the application (Microsoft Dynamics RMS 2.0) that accesses the database in SQL 2005, CPU usage hits 100% and the application often freezes and times out. In the times that the app does not time out, I can access the data through the application without any problems (with the exception of running reports, which are slow and tax the CPU).
The VM is configured for 16gb RAM and 2 socket x 2 core processors (4 total processors).
The machine I migrated this database from is six years old, with 4 gigs of RAM, RAID 1 (mirrored drives) and a single processor. Performance is great :)
Can someone point out where I have a configuration problem/issue? I have ran esxtop and DAVG/rd/wr seldom exceed 15ms (same with GAVG/rd/wr). I initially thought it was a performance issue due to the 7.2K drives, but that doesn't seem to be the problem.
Any advice would be greatly appreciated. Apologies if I don't respond until Monday, it's almost the weekend (thankfully)
Many Thanks,
Tony
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
2
2
9 Comments
Active today
Okay, some advice...
4 vCPUs could be TOO MANY, and if the VM cannot take advantage of the vCPUs, this can have the drastic effect of slowing the VM down, due to the vSMP scheduler within the VM.
So, drop the cpus in the VM to one. Check performance and add another.
Also use Sockets, and not cores (cores are used unless you need to specifically allocate cores for licensing reasons).
I was also going to suggest that 7200rpm drives could be too slow.
Check that you are not operating on a snapshot disk, see my EE Article, CPU utilisation increases when operating on a snapshot.
HOW TO: Performance Monitor vSphere 4.x or 5.0
HOW TO: VCP 5
Checkout Best Practice Guide
http://www.vmware.com/files/pdf/sql_server_best_practices_guide.pdf
SQL Best Practices
http://communities.vmware.com/docs/DOC-13249
Now finally, and this may not be want you want to hear, but this is not the first time or last time, I'll see this on EE or in real life, we've migrated many SQL servers Back to Physical Hardware because the Hypervisor (VMware or Microsoft), "sucks" too much performance away from the VM, causing High CPU when reports are run, causing the CPU to spike at 100%, until the reports have stopped running.
See my video here...
Video - Poor Microsoft SQL Performance caused by Virtualisation - Not all servers are equal!
I've had to obsure the data in the above video, because it had client data in the report, but this was a HP DL360G5 Server, with 64GB RAM, Dual Processor Quad Core Server, with a single VM, with 2 vCPUs, 16GB RAM, Windows 2008 R2 64 bit, and SQL 2008 running a concurrent database (500MB), the host OS was ESXi 5.0. 15k SAS disks.
When a single query was sent to the db, it would lock the VM and Host, at 100% for 11-13mins, when the query was finished CPU would return to normal, the VM and host operations were unusable.
The same server, reduced to 8GB RAM with No Hypervisor (ESXi), completed the query in seconds.
So we migrated back to the physical server.
4 vCPUs could be TOO MANY, and if the VM cannot take advantage of the vCPUs, this can have the drastic effect of slowing the VM down, due to the vSMP scheduler within the VM.
So, drop the cpus in the VM to one. Check performance and add another.
Also use Sockets, and not cores (cores are used unless you need to specifically allocate cores for licensing reasons).
I was also going to suggest that 7200rpm drives could be too slow.
Check that you are not operating on a snapshot disk, see my EE Article, CPU utilisation increases when operating on a snapshot.
HOW TO: Performance Monitor vSphere 4.x or 5.0
HOW TO: VCP 5
Checkout Best Practice Guide
http://www.vmware.com/files/pdf/sql_server_best_practices_guide.pdf
SQL Best Practices
http://communities.vmware.com/docs/DOC-13249
Now finally, and this may not be want you want to hear, but this is not the first time or last time, I'll see this on EE or in real life, we've migrated many SQL servers Back to Physical Hardware because the Hypervisor (VMware or Microsoft), "sucks" too much performance away from the VM, causing High CPU when reports are run, causing the CPU to spike at 100%, until the reports have stopped running.
See my video here...
Video - Poor Microsoft SQL Performance caused by Virtualisation - Not all servers are equal!
I've had to obsure the data in the above video, because it had client data in the report, but this was a HP DL360G5 Server, with 64GB RAM, Dual Processor Quad Core Server, with a single VM, with 2 vCPUs, 16GB RAM, Windows 2008 R2 64 bit, and SQL 2008 running a concurrent database (500MB), the host OS was ESXi 5.0. 15k SAS disks.
When a single query was sent to the db, it would lock the VM and Host, at 100% for 11-13mins, when the query was finished CPU would return to normal, the VM and host operations were unusable.
The same server, reduced to 8GB RAM with No Hypervisor (ESXi), completed the query in seconds.
So we migrated back to the physical server.
Thank you for the great information.
I scaled back the processor to a single socket. Horrible performance. Increased it to two sockets, same horrible performance. And this is just when the application is launched from the local server. CPU hits 100% and stays there until the application times out.
Once in the application, however, performance is quick (again, slowing down when a large report is ran).
I will read through the best practice guides. I have a hard time believing that anything in there will really improve performance. It could be this particular database (which was created before virtualization was mainstream). Or it can just be that this server just cant hack it. This is a 9gb database with no more than 10 concurrent connections at any given time. I am not a SQL expert by any stretch of the imagination, so there may be maintenance that can and should have been done to this database to increase performance, but as I stated before it is running great on the old server.
Just for fun I put this database on my laptop running Win 7 32 bit, 3gb ram Intel i5 processor. I installed SQL 2005. Performance was quick, both locally and accessing from a remote machine.
I am going to have to abandon the hypervisor on the new server, aren't I?
I scaled back the processor to a single socket. Horrible performance. Increased it to two sockets, same horrible performance. And this is just when the application is launched from the local server. CPU hits 100% and stays there until the application times out.
Once in the application, however, performance is quick (again, slowing down when a large report is ran).
I will read through the best practice guides. I have a hard time believing that anything in there will really improve performance. It could be this particular database (which was created before virtualization was mainstream). Or it can just be that this server just cant hack it. This is a 9gb database with no more than 10 concurrent connections at any given time. I am not a SQL expert by any stretch of the imagination, so there may be maintenance that can and should have been done to this database to increase performance, but as I stated before it is running great on the old server.
Just for fun I put this database on my laptop running Win 7 32 bit, 3gb ram Intel i5 processor. I installed SQL 2005. Performance was quick, both locally and accessing from a remote machine.
I am going to have to abandon the hypervisor on the new server, aren't I?
Active today
I'm sorry to say, that not all servers can be virtualised, as much as we would like to think so.
Just for fun, I am converting the current SQL server to the virtual environment, just to see how it performs.
I read the documents you sent me and the server is as optimized as it can be. Will keep you posted, but I suspect for this particular server virtualization may not be an option.
I read the documents you sent me and the server is as optimized as it can be. Will keep you posted, but I suspect for this particular server virtualization may not be an option.
Active 1 day ago
Hanccocka has stated many times (in his comments on this question, and others) that SQL is prone to mysterious performance problems when virtualized. Your seems like an example. But I find it hard to accept such an extreme penalty for virtualization of a workload as light as the one you describe here. I understand that you can't spend an unlimited amount of time trying to solve the mystery, but it is clear that there is an underlying issue here that none of us understand. You should be able to virtualize this system.
I agree with you, Carlo.
I finished with the P2V of the current production server. It performs flawlessly. No issues with SQL connections both locally and from client machines. This is a Server 2003 machine running SQL 2005.
What am I to make of this?
I finished with the P2V of the current production server. It performs flawlessly. No issues with SQL connections both locally and from client machines. This is a Server 2003 machine running SQL 2005.
What am I to make of this?
Active 1 day ago
I would love to know what made the other instance perform so badly. Generic statements like "don't virtualize SQL" don't really help anybody. But I'm afraid I have no idea what caused the issue you ecountered.
Are you sure there was no contention for memory? You mentioned you had given the SQL VM 16GB on a hypervisor with 32GB. Were there other VMs totaling more than 12GB on the server? Hypervisors are generally good at sharing CPUs, but bad at sharing overcommited memory.
Are you sure there was no contention for memory? You mentioned you had given the SQL VM 16GB on a hypervisor with 32GB. Were there other VMs totaling more than 12GB on the server? Hypervisors are generally good at sharing CPUs, but bad at sharing overcommited memory.
No, there is one other machine on the hypervisor, with 8gb of RAM assigned to it. This new P2V machine has 4gb assigned to it.
The P2V server continues to perform very well, the Server 2008 VM continues to struggle. Other than the OS, the only other difference is that the 2003 server is 32-bit and the 2008 server is 64-bit. Same with the versions of SQL.
The P2V server continues to perform very well, the Server 2008 VM continues to struggle. Other than the OS, the only other difference is that the 2003 server is 32-bit and the 2008 server is 64-bit. Same with the versions of SQL.
Just more info. I created a 64-bit Win 7 VM w/ 2 proc and 8gb RAM. Installed SQL2005 and the RMS application. The vm performed flawlessly. Clients connected without issue and performance was excellent. So, yeah, I either missed something (or botched something) in the Server 2008 install (can't imagine what) or there is an issue with this database/sql2005/server200
Featured Post
Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Background Information
Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
This article explains how to install and use the NTBackup utility that comes with Windows Server.
This tutorial will walk an individual through locating and launching the BEUtility application to properly change the service account username and\or password in situation where it may be necessary or where the password has been inadvertently change…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Suggested Courses
Course of the Month6 days, 9 hours left to enroll
724 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.