SQL Server Performance Forum – Threads Archive
Avg Disk Queue
We have a SAN that has 144/146 drives (RAID5). The SAN has been configured as one big physical drive and 3 logical partitions, each are striped across all the drives. When we run a perfmon we get an Avg Disk Q Len of anywhere from 14 to 30 depending on the time day, we will experience peak values around 1500+. I understand that your supppose to divide the AVG Q by the number of drives but when it’s configured with multiple partitions and several servers (not all sql servers) all sharing the same SAN does this still hold true? Can one get an accurate number with this configuration? Should we use the Avg Disk Transfer/sec instead? We are getting Avg Disk Transfer/sec at .535, this should be under .3? We are also getting a Disk Queue Time of 7.49, compared to a dedicated raid array with 10 drives of .076. I am fairly positive we have an I/O bottleneck but just using the Avg Q Len doesn’t prove that? Any insight would be greatly appreciated.you have really really seriously bad io problems.
the whole purpose of the architecture of modern database systems (separate data & log disk) is to overcome disk latency problems, the disk latency on the log should read 0ms on perfmon (actual of probably 0.2ms, ie. 0.0002sec)
the disk latency on reads from data (away from checkpoints) should be in 7-20ms range, not 535ms. the peak value during checkpoints can go much higher, but the duration of the checkpoint should be very short (5-10sec) depending on your business needs. the absolute disk queue values are not as easy to set bounds for because of the nature of the SAN it almost sounds as if you had the SAN vendor rep determin configure, which is the absolute worst possible thing that can be done for a database (SQL, Oracle, DB2 etc)
I agree with joe – this is seriously wrong. When we first installed our SAN we had very bad IO response times – it turned out that the LUN’s did not have caching enabled. But your problem looks far worse. Even though you are on a SAN you should still seperate physical disk for logs and data. The same priniciples still exist as local drives, ie RAID 0+1 is better than RAID 5, seperate the tempdb etc etc. It depends how far you want to go. I strongly suggest you contact your vendor and demand some answers on the performance though.
]]>