VMWare ESXi v4.1 disk performance issues on Dell 2950

I'm having SQL performance issues on a ESXi v4.1 host.  It's installed onto a Dell 2950 with a Perc 5i controller.  All VMs are stored in a RAID 10 array.  The VM that seems to be having performance issues is a SQL 2005 32 bit VM.  The DB that is having the performance issues is only 500 MBs in size...  I'm getting 100 and 65 average milliseconds on two of the drives...  The rest are pretty low...  This seems high, right?
gopher_49Asked:
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.

Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
How many spindles in the array?

How much memory and vCPU assigned to SQL VM?

The VM is not running on a snapshot disk?
0
gopher_49Author Commented:
There are 4 disks in the array.  I have 4 GBs of RAM and 4 x vCPU's.  The VM is not running on a snapshot.
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
4 disks is not many, have you tried, reduc-ing and increasing the vCPUs?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

gopher_49Author Commented:
I only have 4 x 1 TB drives in the RAID 10 array for I thought ESXi v4.1 couldn't have LUNs larger than 2 TBs?  Do you mean reducing it from 4 vCPU's down to 1 and then increasing again?  How will that help with disk I/O?
0
dipopoCommented:
I'd run esxtop to check the DAVG/KAVG values and to be honest I usually stay away from MS SQL databases being on virtual disks. Kernel overhead for scsi commands.
0
gopher_49Author Commented:
This is such a small environment I didn't think SQL would be a problem.  There are maybe 12 users that access the DB.
0
dipopoCommented:
Did you check what is happening via esxtop? this will at least shed more info on why the performance is bad.

ssh to the host and run esxtop, type u and f to (de)select the necessary counters.

Current Field order: ABcdeFGhIjklmnop

* A:  DEVICE = Device Name
* B:  ID = Path/World/Partition Id
  C:  NUM = Num of Objects
  D:  SHARES = Shares
  E:  BLKSZ = Block Size (bytes)
* F:  QSTATS = Queue Stats
* G:  IOSTATS = I/O Stats
  H:  RESVSTATS = Reserve Stats
* I:  LATSTATS/cmd = Overall Latency Stats (ms)
  J:  LATSTATS/rd = Read Latency Stats (ms)
  K:  LATSTATS/wr = Write Latency Stats (ms)
  L:  ERRSTATS/s = Error Stats
  M:  PAESTATS/s = PAE Stats
  N:  SPLTSTATS/s = SPLIT Stats
  O:  VAAISTATS= VAAI Stats
  P:  VAAILATSTATS/cmd = VAAI Latency Stats (ms)

Toggle fields with a-p, any other key to return:
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
Either you have too many or too few vCPUs.

What is CPU like?
0
gopher_49Author Commented:
I have 4 x vCPU's for the VM and they are 1-3% utilization throughout the day.
0
gopher_49Author Commented:
I'll check esxtop later this weekend.. Or I guess I'll check when everyone is connected for more accurate results.
0
gopher_49Author Commented:
I have a little more info on the issue..  The application their using loads the data just fine..  And working through the application seems okay.. But..  If they do a search it's really fast..  But.. Once they load the search results and click through them after about a half minute of clicking it starts to lag really bad..  If you close the search and do it again at first it's fast but then scrolling through the records it gets slow again.  Any ideas?
0
dipopoCommented:
Hmmm could this be a tempdb issue me thinks.

two things we need to do to confirm this or otherwise.

1. Run ESXTOP and check the CONs and DAVG values [Please post this here as well]
2. Get Idera and check the SQL server performance.

http://www.idera.com/productssolutions/freetools/sqlcheck
0

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
gopher_49Author Commented:
Question.. I have a PERC 5i controller.  I have a mirror and a RAID 10.  Would it help moving the SQL DBs to the mirror? I don't have any VMs on there and the VMs I do have on there have little to no disk I/O.  Attached is screenshot of ESXTOP.

ESXTOP
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
it would certainly be worth a try, if it's I/O RAID related.
0
gopher_49Author Commented:
Attached is the Idera screenshot.

Idera
0
gopher_49Author Commented:
Here's another..  It seems on the writes my SQL takes a hit  on the wait.. I see up to 3 second waits when writes occur.  Below is an example.

Idera2
0
gopher_49Author Commented:
I just disabled GFI MailArchiver services and my WAIT spikes went from 3000-2000 down to 3-2.. And..  When testing via the developer/end users they cannot reproduce the symptoms..  GFI MailArchiver is simply requires too much I/O in my virtualized environment..  Can I purchase a larger controller for the Dell 2950?  Or will I also need SATA 3 drives or SAS versus the SATA Seagate ES.2 drives that I have now?
0
dipopoCommented:
SAS ~140 IOPS would be best SATA will only give you ~70 IOPS.

But I would seriously not jump to conclusion yet! To achieve usable IOPS especially for the duration of the platform.

I would look at SSD PCI cards or Shared Storage with some disk count using SAS 15K disks. Now that is high end.

Else buy some SAS 15K disks to replace current SATA.
0
gopher_49Author Commented:
It's safe to assume SAS 15K is out of budget.. And..  Shared storage forget about it.. So, I'll move GFI MailArchiver (huge resource hog) from the RAID 10 array to the mirror.  If it still results in poor performance on their other SQL DB / APP I'll simply utilize a server we have that's currently powered down..  I'll update the ticket once the end users run their mission critical app for the remainder of the day.  I'm 99% sure it's performing well now.
0
gopher_49Author Commented:
Idero proved SQL performance issues..  Turned off apps ones by one until SQL ran well again.
0
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
VMware

From novice to tech pro — start learning today.