Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-07-08 : 11:59:53
|
| Manu writes "Hi,we have a 60 GB sql 2K database on a RAID 5 disk with 260 GB of usable HD. the server is dell poweredge 4 cpu x 1.2 GHz P4 with 4 GB RAM.When we run the aspx app, we have decent performance but sporadically during the day, the system just slows down to a crawl with response times increasing from milliseconds to minutes. Simple "Select top 1 * from tablename" queries take several seconds to come back for random tables.I have been told that performance latency accompanies RAID 5 configurations but there are no solutions i have heard to fix this issue. We have revisited the data model several times and added indexes, removed indexes, optimized all queries, regularly defragged indices, rebuilt them..... but nothing works. System is just as temperamental as always - slowing down randomly and then speeding up..Help please...Thanks in advancea stranded sql user.." |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-07-08 : 12:32:48
|
| Well, that RAID 5 array for a 60GB database is part of this problem.You need to at least separate your Transaction logs into their own RAID 1 or RAID 10 array. Once you do that, that should be a big help. Then, if you have the $$, move your data from RAID 5 to RAID 10.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-08 : 13:28:39
|
| Systems don't slow down randomly. That's an impossibility. You need to seperate your log and data files like Michael said and use RAID 10 for the log files especially. More RAM never hurts assuming you're on Enterprise Edition. You need to set up Performance Monitor and Profiler, so you capture:1. Key indicators to find out where your primary bottleneck is (when you get that fixed, it might uncover more).2. Queries that run long or consume a lot of CPU, READS, and WRITES.3. Deadlocks that could be causing contention, buffer, and memory issues.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-07-08 : 13:59:28
|
| Well....what's going on on the server when this happens? Are you running perfmon or profiler to see what else might be happening? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-07-08 : 14:59:17
|
| Yeah, I should have mentioned that. Run profiler to see what is taking so long and causing this bottleneck. Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
manupuri
Starting Member
1 Post |
Posted - 2004-07-12 : 19:24:53
|
| Thank you for the reply..We found the problem though. You were right - all of you, some #$@*&^% idiot had a disk defragmentation software turned on to start at 9 AM in the morning - just at the time when the system load is at it's highest. Performance improved after disabling it. I will still put in some perf counters to monitor the system going forward. Thanks again.Manu |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-07-12 : 19:56:34
|
| Bad admin!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
|
|
SQL Server deadlocks