Hello, We have a typical RAID 10 configuration with 4 HDD on one of our production database server where I observed in perfmon for the drive which is configured with RAID 10 configuration. Avg. Disk Read Queue Length - 12.51 Avg. Disk Write Queue Length - 0.06 Somewhere I read , I should divide these figures with the no of spindles. Now the question is , should I divide these values by 4 or 2 and what should I interprete from these values ? Thanks, Hemant
I believe you should divide it by no of spindles, i.e by 4. Anything more than value 2 indicates bottleneck. But its better to check other related counters like Avg.DiskRead time and Avg.Disk WriteTime to find the bottleneck. For the data files of an OLTP Database ideally Avg.DiskReadTime\WriteTime should should be less than 20Ms and for OLAP 20-30Ms is fine. For the Log file it should 5ms. If you experience disk bottlenecks, check your database for Missing Indexes,index fragmentation, outdated statistics, long running queries, blocked process, waiting task and tune them Also check weather your log files, Data files and OS files are on separate disk.
In isolation there isn't anything to read into these figures. You need to consider the duration over which the average has been calculated and what the activity on the server was for that time. Was it quiet (night time?) from a business point of view, were backups running, were there 10 or 1000 users connected? Did the users think the system was slow, normal or fast? Collect all of this information and record it somewhere safe, then collect it next week, month or quarter and compare the values over time. If the system is busier and the disk perf is similar then you can say that the hardware is not an issue - its coping with extra activity. If the system was very slow for the users then you need to investigate what was going on and whether when the system is considered fast the metrics are different. I would recommend reading the methods that Brent Ozar uses to benchmark and analyse system performance, read his work at [
You also have to take into account the wait states on the server. You might see disk queuing, but not see any waits on disks. If nothing is waiting on the disk, the queuing may not matter for performance (it might matter a bit for recoverability because if stuff hasn't finished writing when an outage occurs, it's lost).