Very Slow SQL Performance

[bbarnes]

We have recently gone virtual on our SQL 2000 server that powers our ERP. Our setup is that we are using the IBM Bladecenter H chassis with HS21 blades, two cisco modules in the bladecenter connected to a 4500 for network traffic and two copper pass thru modules connected to a pair of Cisco 3750s. The Cisco 3750s are also connected to two Dell/Equallogic PS300E arrays. We are using Qlogic 4052 HBAs inside each blade to connect iSCSI. We have been running fine on this setup for several months until we moved our hard-hit SQL server into the mix. The SQL server is showing extremely high dsik queue lengths even when we have our transaction log and data files on seperate RDM's going over different paths. I"m just wondering if anyone can think of a solution, are the arrays enough to handle the load being given to them (from what I understand, the equallogic group spreads the volumes over the two boxes. I am eagerly looking for any possible solutions.

[Ken_Cline]

How are your SQL VMs configured? How much vRAM, how many vCPUs? I assume you have VMware Tools installed? What else is running on the hosts - what is their (hosts) utilization percentage?

Ken Cline

Technical Director, Virtualization

Wells Landers

VMware Communities User Moderator

[bbarnes]

We have 12 GB of vRAM dedicated to the SQL server. That is the only virtual machine running on that physical machine at the moment. The physical machine has a total of 16 GB of physical ram. It has 4 vCPUs and yes, VMware tools is installed. Host memory usage sits between half and 2/3 usually and CPU usually never gets above half.

Part of me is starting to wonder if the spindles in our array isn't able to handle the load. Each array has 14 drives (12 used) that are running at 7200 rpm. Like I said earlier, we run everything we do on these two arrays, another SQL server, document imaging system, exchange, etc.

[Ken_Cline]

How many pCPUs were on the physical systems hosting SQL prior to migration? How many pCores are on the host? Have you looked at SQL Server Performance in a VMware Infrastructure 3 Environment? Another interesting read is Ten Reasons Why Oracle Databases Run Best on VMware (even though it talks about Oracle, a lot of it still applies).

Ken Cline

Technical Director, Virtualization

Wells Landers

VMware Communities User Moderator

[bbarnes]

I believe there were two pCPUs on the old machine. We didn't do a P2V on that machine. We just did a fresh install of SQL on a new VM and attached our databases to it.

[rickwestrate]

Focus on you disk sub-system and the connectivity to your disk. You mentioned that your disk queue length were high. That's a dead give away.

Take a hard look at the IO capabilities of your SQL system before you migrated it compared to what you have now. You mentioned that you split out the DB and Log disks, but you did not clarify if you put those both back on the same VMFS volume. If so, you really didn't do much to seperate the IO patterns as both virtual disks are talking to the same VMFS volume (and LUN) on the underlying system. The EQL arrays now have a way to create seperate disk pools within the array. This would allow you to truly seperate out the IO. I would also reccomend engaging VMware and EQL tech support for assistance to better characterize your storage IO patterns and possible bottlenecks.

[bbarnes]

We put an RDM for the log file on one LUN and another RDM for the data on a differnt LUN. I believe on the physical server, it had a dedicated array of 7 10k drives. Now we have the two EQL boxes with 12 usable drives at 7.2K. We have engaged every type of help possible, IBM, VMware, EQL and no one seems to be able to help with the issue which is why I turn to the people who actually use this stuff.

[rickwestrate]

Understood. You may need to ask for an escalation.

While you wait for that, please take a quick look at for some more information on using esxtop. It can be very helpful to look at the performance levels from that level as well. To look at disk stats, open esxtop then "d" for a disk view or "v" for a VM view (3.5 only on that one). In addition to see disk latency numbers you will need to issue a "-a" to esxtop as disk latency is not displayed by default.

I hope this helps a bit!

[ToddMuirhead]

It sounds to me like you might need more disks. Moving from dedicated 10K RPM disks to shared 7.2K RPM disks might not be providing enough performance - even though there are more disks. It's really impossible to know without looking at some more performance info. The EqualLogic array should be able to give you some more info. in the EqualLogic web management tool - Tools (bottom left of the GUI) - Performance Monitor will show performance at the array level for physical disks, contorller NICs, and more. This help you determine if the array is overloaded or not.

Todd

[oreeh]

FYI: this thread has been moved to the Performance Forum.

Oliver Reeh[/i]

[VMware Communities User Moderator|http://communities.vmware.com/docs/DOC-2444][/i]