Solved

poor performance when writing from application to sql database

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

805 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