?
Solved

poor performance when writing from application to sql database

Posted on 2014-01-24
4
Medium Priority
?
271 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
[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 Comments
 
LVL 47

Accepted Solution

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

Four New Appliances. Same Industry-leading Speeds.

But don't take it from us.  The Firebox M370 is Miercom tested and Miercom approved, outperforming its competitors for stateless and stateful traffic throughput scenarios.  Learn more about the M370, M470, M570 and M670 and find the right solution for your organization today!

Question has a verified solution.

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

Popular third-party chat platforms like Slack, Discord, and Telegram are just a few of the many new productivity applications that are being hijacked by cybercriminals to create command-and-control (C&C) communications infrastructures for their malw…
Let’s face it: one of the reasons your organization chose a SaaS solution (whether Microsoft Dynamics 365, Netsuite or SAP) is that it is subscription-based. The upkeep is done. Or so you think.
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 INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

764 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