Solved

server performance optimization

Posted on 2012-03-29
18
454 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 33

Assisted Solution

by:paulmacd
paulmacd earned 250 total points
Comment Utility
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 117

Expert Comment

by:Andrew Hancock (VMware vExpert / EE MVE)
Comment Utility
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
Comment Utility
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
Comment Utility
Which version of SQL and which version of Windows server?
0
 

Author Comment

by:gatorIT
Comment Utility
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
Comment Utility
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
Comment Utility
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 33

Expert Comment

by:paulmacd
Comment Utility
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
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 33

Expert Comment

by:paulmacd
Comment Utility
Couldn't hurt to try.  It may be indicative of some other problem though, like a controller issue.
0
 
LVL 117

Expert Comment

by:Andrew Hancock (VMware vExpert / EE MVE)
Comment Utility
swap SATA to SAS, more spindles the better RAID10
0
 
LVL 47

Accepted Solution

by:
dlethe earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 117

Expert Comment

by:Andrew Hancock (VMware vExpert / EE MVE)
Comment Utility
So additional IOPS may have helped?
0
 

Author Comment

by:gatorIT
Comment Utility
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 117

Expert Comment

by:Andrew Hancock (VMware vExpert / EE MVE)
Comment Utility
Glad youve resolved your issues.
0
 
LVL 47

Expert Comment

by:dlethe
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

#Citrix #Citrix Netscaler #HTTP Compression #Load Balance
Moving your enterprise fax infrastructure from in-house fax machines and servers to the cloud makes sense — from both an efficiency and productivity standpoint. But does migrating to a cloud fax solution mean you will no longer be able to send or re…
Teach the user how to join ESXi hosts to Active Directory domains Open vSphere Client: Join ESXi host to AD domain: Verify ESXi computer account in AD: Configure permissions for domain user in ESXi: Test domain user login to ESXi host:
In this video tutorial I show you the main steps to install and configure  a VMware ESXi6.0 server. The video has my comments as text on the screen and you can pause anytime when needed. Hope this will be helpful. Verify that your hardware and BIO…

772 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

13 Experts available now in Live!

Get 1:1 Help Now