Solved

poor performance when writing from application to sql database

Posted on 2014-01-24
4
265 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:Scott Pletcher
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
LTrim & Double Space Correction 5 42
SQL Dump exec output to table 3 23
First Max value 3 32
MS SQL + group by time 4 15
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
There's a better way to communicate time sensitive or critical info.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

821 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