?
Solved

server performance optimization

Posted on 2012-03-29
18
Medium Priority
?
460 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 1000 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 122
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:David
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
Automating Your MSP Business

The road to profitability.
Delivering superior services is key to ensuring customer satisfaction and the consequent long-term relationships that enable MSPs to lock in predictable, recurring revenue. What's the best way to deliver superior service? One word: automation.

 
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:David
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 122
ID: 37783789
swap SATA to SAS, more spindles the better RAID10
0
 
LVL 47

Accepted Solution

by:
David earned 1000 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 122
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 122
ID: 37798380
Glad youve resolved your issues.
0
 
LVL 47

Expert Comment

by:David
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Teach the user how to use configure the vCenter Server storage filters Open vSphere Web Client:  Navigate to vCenter Server Advanced Settings: Add the four vCenter Server storage filters: Review the advanced settings: Modify the values of the four v…
This video shows you how easy it is to boot from ISO images for virtual machines with the ISO images stored on a local datastore on the ESXi host.
Suggested Courses

771 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