asked
Physical Disk Performance measurement
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.
As per my understanding,in a 2+2 RAID 10 configuration only two spindles are used for striping the data and other two are used only for mirroring ,if it is so then Avg. Disk Write Queue Length should be divided by 2 but not sure how many spindles are used in case of reading data from the disk array. There is similar post available and after reading it ,I got confused by the answer given by DBADave-
http://www.sqlservercentral.com/Forums/Topic887787-146-1.aspx#bm888520 I also checked some other counters like Average Disk sec/Read and Average Disk sec/Write and their values are 30.25 and 8.47 respectively. I have been collecting this data for last 10 days through perfmon counter log scheduled to run in peak hours.
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 [
http://www.brentozar.com/go/faster][1] [1]:
http://www.brentozar.com/go/faster
I think you provided a great answer, but you touched on something even more significant. "Did the users think the system was slow, normal or fast? " Performance is relative. I will often obsess over optimizing a procedure just to see how fast I can make it go and what I can learn along the way, but that is really not important from a business perspective. If your users think your system is fast, then it is. If your users think the system is slow, then it is.
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).
