Solved

server performance optimization

Posted on 2012-03-29
18
458 Views
Last Modified: 2016-11-23
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?
0
Comment
Question by:gatorIT
[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
  • 4
  • 4
  • 4
  • +2
18 Comments
 
LVL 34

Assisted Solution

by:Paul MacDonald
Paul MacDonald earned 250 total points
ID: 37781921
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
 
LVL 120
ID: 37781937
if SQL is performing poorly under VMware Hypervisor consider testing the same SQL server on bare metal, with NO hypervisor.
0
 
LVL 47

Expert Comment

by:dlethe
ID: 37781951
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
Don't miss ATEN at NAB Show April 24-27!

Visit ATEN at NAB Show to learn how our "Seamlessly Entertaining" solutions deliver fast, precise video streaming without delays for the broadcasting and media environment. ATEN will showcase its 16x16 Modular Matrix Switch (VM1600) and KVM Over IP Solution (KE6900 series).

 
LVL 30

Expert Comment

by:pgm554
ID: 37782151
Which version of SQL and which version of Windows server?
0
 

Author Comment

by:gatorIT
ID: 37782174
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
 
LVL 30

Expert Comment

by:pgm554
ID: 37782199
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
 
LVL 47

Expert Comment

by:dlethe
ID: 37782202
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
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 37782221
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
 

Author Comment

by:gatorIT
ID: 37783760
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
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 37783770
Couldn't hurt to try.  It may be indicative of some other problem though, like a controller issue.
0
 
LVL 120
ID: 37783789
swap SATA to SAS, more spindles the better RAID10
0
 
LVL 47

Accepted Solution

by:
dlethe earned 250 total points
ID: 37784019
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
 
LVL 30

Expert Comment

by:pgm554
ID: 37784427
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
 

Author Comment

by:gatorIT
ID: 37798343
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
 
LVL 120
ID: 37798357
So additional IOPS may have helped?
0
 

Author Comment

by:gatorIT
ID: 37798375
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
 
LVL 120
ID: 37798380
Glad youve resolved your issues.
0
 
LVL 47

Expert Comment

by:dlethe
ID: 37798538
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

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

HOW TO: Upload an ISO image to a VMware datastore for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere Host Client, and checking its MD5 checksum signature is correct.  It's a good idea to compare checksums, because many installat…
Advanced tutorial on how to run the esxtop command to capture a batch file in csv format in order to export the file and use it for performance analysis. He demonstrates how to download the file using a vSphere web client (or vSphere client) and exp…
This Micro Tutorial walks you through using a remote console to access a server and install ESXi 5.1. This example is showing remote access and installation using a Dell server. The hypervisor is the very first component of your virtual infrastructu…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question