Solved

poor performance when writing from application to sql database

Posted on 2014-01-24
4
269 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:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Both MMF (multi-mode fiber) and SMF (single-mode fiber) are types of optical fiber that can aid in communication applications. These thin strands of silica or glass will allow communication to occur between devices. The transmission of light between…
We look at whether swapping a controller board on a failed hard drive is likely to solve the problem.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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…

737 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