server performance optimization

We’re trying to improve performance of SQL and RDS in our VMware environment for ~50 users that consists of two Dell R710’s, which I’ll call R710-A and R710-B.  SQL intensive operations from our line of business app can take several seconds to complete, which is longer than when there is no load.  

R710-A:
2 x Xeon E5620 CPUs (2.4 GHz)
16 GB RAM
H700 RAID Controller (512 MB)
VD1-RAID 1: 2 x 600 GB SAS 15K
VD2-RAID 1: 2 x 750 GB SATA
Roles: Domain Controller/Exchange

R710-B:
2 x Xeon E5620 CPUs (2.4 GHz)
32 GB RAM
H700 RAID Controller (512 MB)
VD1-RAID 1: 2x 146 GB SAS 15K
VD2-RAID 10: 4x 600 GB SAS 15K
Roles: MSSQL (VD1), RDS (VD2)

No issues with R710-A, only list that for informational purposes and because it does have capacity for four more physical disks.

On R710-B, the MSSQL server has adequate RAM (16 GB allocated, always > 4 GB available) but I believe the single RAID-1 volume is the obvious bottleneck.  

I have tried moving the MSSQL data files to the RAID 10 volume (keeping logs on the RAID 1) but no significant impact.

Options:
1.      One option is to move the 600 GB SAS disks from R710-A to R710-B and create a 6-disk RAID 10 volume.  RDS and MSSQL would be all on the same virtual disk.
2.      We also have access to a R510 with 8 SATA disks.  We could either create two 4-disk RAID 10’s or a big 8-disk RAID 10 (I’ve read the single RAID 10 with more drives is faster).  Only problem is this server only has one CPU, slower PERC controller with 256 MB cache (compared to H700 with 512) and as mentioned SATA disks vs. 15K SAS.

Questions:
1.      Which option above would we see the most benefit from?
2.      Since our limitation seems to be with physical disks (only 6 in the R710), where would we be best served by our dollars – bringing the R510 up to speed by adding a CPU and swap the SATA drives for SAS?  I don’t mind swapping disks but I don’t want to get into swapping CPU’s.
3. Any other thoughts or comments?
gatorITAsked:
Who is Participating?
 
DavidPresidentCommented:
Now that is more like it, a nice long sample period that gives good info.
More IOPs is a given.   If you have the free drive bay slots, I would save myself time and get 2 SAS drives, configure as RAID1 and then start migrating over scratch table spaces, index files, swap, etc...
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Are you sure you're disk bound?  Have you profiled this machine (either with Performance Monitor, SQL Server Profiler, or some third party tool)  to see where the bottlenecks are?
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
if SQL is performing poorly under VMware Hypervisor consider testing the same SQL server on bare metal, with NO hypervisor.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DavidPresidentCommented:
You are just wasting time trying to improve performance w/o taking the time to identify the bottleneck(s).

Run perfmon and let the O/S tell you what is hitting the ceiling, address that, and repeat the process.
0
 
pgm554Commented:
Which version of SQL and which version of Windows server?
0
 
gatorITAuthor Commented:
What performance counters do you recommend?  

In a limited testing window here's what we have so far:
Avg Disk sec/Read average is 0.004, max 0.016.
Avg Disk sec/Write average is 0.000, max 0.003.
Avg Disk Queue Length average is 0.033, max 2.768

I think the Disk Queue should be < 2 with two disks?

SQL 2005, Windows Server 2003 SP2
0
 
pgm554Commented:
Before you set up the server ,did you run the diskpar command?

You probably need to align the disks.

http://msdn.microsoft.com/en-us/library/dd758814%28v=sql.100%29.aspx
0
 
DavidPresidentCommented:
The I/O information indicates that you really don't have an I/O problem.  
But the I/Os take a long time ... so this looks like poorly written SQL code.

Is this your SQL app?  If so, then I'd get your developers looking at your application and if they can do things more efficiently.  They probably have a lot of automated triggers and such that are bogging it down by requiring a lot of processing in cached memory.
0
 
Paul MacDonaldDirector, Information SystemsCommented:
I agree.  The numbers look okay on the disk side.  You may glean more information by running the SQL Profiler or a few other Performace counters  (CPU, network)
0
 
gatorITAuthor Commented:
I have no doubt the SQL code is not written very well but the app is from a third party vendor.  That's something that isn't going to change/improve anytime soon I don't think.

We have had 19 instances of Avg Disk Queue Length > 2 in the past four hours.  This is on the MSSQL partition on the RAID 1 volume.
49
3
11
415
5
133
27
2
26
456
2
3
14
63
121
95
22
25
16

Wouldn't adding IOPS help this?
0
 
Paul MacDonaldDirector, Information SystemsCommented:
Couldn't hurt to try.  It may be indicative of some other problem though, like a controller issue.
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
swap SATA to SAS, more spindles the better RAID10
0
 
pgm554Commented:
Have you tried to test and see if you have a vm alignment problem?

http://nickapedia.com/2011/11/03/straighten-up-with-a-new-uber-tool-presenting-uberalign/
0
 
gatorITAuthor Commented:
We added two disks and have our SQL & RDS servers running on a single RAID 10 array (6 x 600 GB 15K SAS) rather than a RAID 1 for SQL and 4-disk RAID 10 for RDS.  We've had no issues so far.
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
So additional IOPS may have helped?
0
 
gatorITAuthor Commented:
Yes, I initially separated SQL and RDS with two virtual disks because I was concerned about contention (due to aforementioned un-optimized SQL code).  All else being equal, a 6-disk R10 appears to beat a R1+R10 (4-disk).
0
 
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
Glad youve resolved your issues.
0
 
DavidPresidentCommented:
Tuning raid for a VM and a physical machine are profoundly different.  Had this been a physical machine, and the RAID built properly you would have likely seen at least twice the performance out of a R1+R10 vs a 6-disk R10.  But moot point. glad you are happy.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.