Solved

server performance optimization

Posted on 2012-03-29
18
455 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
  • 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 118
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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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 118
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 118
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 118
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Exchange server is not supported in any cloud-hosted platform (other than Azure with Azure Premium Storage).
In this article, I am going to show you how to simulate a multi-site Lab environment on a single Hyper-V host. I use this method successfully in my own lab to simulate three fully routed global AD Sites on a Windows 10 Hyper-V host.
Teach the user how to configure vSphere Replication and how to protect and recover VMs Open vSphere Web Client: Verify vsphere Replication is enabled: Enable vSphere Replication for a virtual machine: Verify replicated VM is created: Recover replica…
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now