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 |
|
rerichards
Starting Member
10 Posts |
 Posted - 2006-01-25 : 21:13:35
|
| I am running SQL 2K enterprise, using RAID 5 with 6 disks. I returned the following performance counters and was hoping somebody could tell me if there are any signs of disk bottlenecks. The values specified are in the order of Average, Minimum, and Maximum.Physical Disk: % Disk Time 145.839, .576, 400.861Physical Disk: % Disk Read time 1, 0, 8Physical Disk: % Disk Write time 70, 0, 456Physical Disk: Avg. Disk Queue Length 1, 0, 9Physical Disk: Avg. Disk sec/Transfer 0, 0, 0Physical Disk: Current Disk Queue Length 4, 0, 208Physical Disk: Disk Bytes/sec 1269170, 30323, 7073880Physical Disk: Disk Transfers/sec 77, 5, 257 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-01-25 : 22:03:14
|
| I would only look at current disk queue length. That counter really tells the story about what is going on with your disks.Basically, you want to make sure that you do not have a sustained current disks queue. If you have a current disk queue that is always above 0 (and never returns down to 0) then that means you are bottlenecked on your disk.For example, if your CDQ fluctuates between 0 and 2 with occasional spikes to 10 then that means that your disks are busy but things are basically under control. Occasional spikes are ok and they are going to happen. It is when you see sustained activity on your drives that you need to take some action.-ec |
 |
|
|
rerichards
Starting Member
10 Posts |
Posted - 2006-01-25 : 22:33:35
|
quote:
Originally posted by eyechart
If you have a current disk queue that is always above 0 (and never returns down to 0) then that means you are bottlenecked on your disk.
As far as a resolution to the bottleneck, I could:1. Obtain faster drives.2. Add drives to the array.3. Create indexes and primary data file in separate filegroups, saving the separate filegroups onto separate disks.Does that sound like a good plan? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-01-26 : 11:35:38
|
| I'd also look at Disk read bytes / sec and Disk Write bytes per second.That will help tell you if you need to move away from RAID 5, and move towards multiple arrays of different types or move to a single RAID 0/1 array.Most probably, you need to separate out your TX Logs to a RAID 1 or RAID 0/1 array. Since those are sequential writes basically, you take a penalty for having that data on a RAID 5 array since it writes slow and reads fast.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
funketekun
Constraint Violating Yak Guru
491 Posts |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-23 : 23:40:37
|
| Will not get better performance. |
|
|
|
|
|
|
|
|
Physical Disk bottleneck??