Solved

poor performance when writing from application to sql database

Posted on 2014-01-24
4
262 Views
Last Modified: 2014-03-16
Hi,
we have a GPS monitoring system , which connected to  devices which send data always 24/7
we have poor performance while writing to SQL ,we cannot change the structure of the application , and the hard disk report by scom is not perfect but "there is problem with the hard disk"
we use unfortunately RAID 5 Configuration with three SAS HDD  
please advice  how to increase the performance

see scom report
Untitled.png
0
Comment
Question by:Kamal Khaleefa
4 Comments
 
LVL 47

Accepted Solution

by:
dlethe earned 250 total points
ID: 39807896
Your problem is inherent in RAID5  with a 3-drives.  Basically it is unworkable. Go with a 2-disk RAID1 or 4-disk RAID10.  Period. You'll see 2-3X minimum maybe 10X or more improvement if you did nothing more than went 2-disk RAID1.

Further performance improvements will depend on what O/S you use, controller, and make/model of HDD & filesystem settings.

But no matter what, it is the RAID5 on that config.
0
 
LVL 30

Expert Comment

by:pgm554
ID: 39807917
What's the controller?
Any cache?
If yes,write through or write back?

OS?
SQL version?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39807999
If you're doing almost all writes, you will likely always periodically see I/O performance issues with RAID5 and only 3 physical drives.

To minimize it:
1) if you are able to, set the write cache% much higher than normal, say 80-90%. [some drives allow write vs read cache ratio to be specified and some don't.]
2) change to SIMPLE recovery model, if possible
3) review indexes, esp. clustering indexes, to reduce the I/O required as much as possible.
0
 
LVL 30

Assisted Solution

by:Duncan Meyers
Duncan Meyers earned 250 total points
ID: 39809919
The graphs you've posted show a spike in queue length for D: and higher than I'd like to see queues for D:. As dlethe suggests, changing the RAID type will help resolve the issue but there's not enough information in what you've posted to give you an idea of how many disks you need. How many SQL transaction per second is the app trying to perform?
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
We need a new way to communicate time sensitive or critical info.   The best part of my role at xMatters is visiting our clients all over the world to learn about how they operate their businesses, share insights that xMatters has gleaned across…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 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

24 Experts available now in Live!

Get 1:1 Help Now