Measuring I/O Performance with System Monitor
Measuring I/O Performance with System Monitor
By : Kasim Wirama, MCDBA, MCITP, MVP SQL Server
System monitor has a bunch of counters for measuring environment subsystem such as processor, memory, disk and SQL Server subsystems. To measure how well your storage subsystem performs to serve application request into its database server, system monitor provides list of informative system counters. Here they are :
- Avg. Disk Queue Length, Avg. Disk Read Queue Length, and Avg. Disk Write Queue Length
These performance counters are used to indicate how many queues regarding to disk requests. High values indicate that the disk is over stressed. Recommended value for them is at most one or zero is even better.
- Avg. Disk Sec/Read, Avg. Disk Sec/Transfer, and Avg. Disk Sec/Write
They give information about latency of I/O. the results is average over a period. When it is converted into mili second, you can compare them to results from SQLIO test result.
- Disk Bytes/Sec, Disk Read Bytes/Sec, and Disk Write Bytes/Sec
They give information about throughput in Bytes/Sec. when it is converted into MB/Sec, you can compare them to results from SQLIO result.
- Disc Reads/Sec, Disk Transfers/Sec and Disk Writes/Sec
They give information how many I/O operations per second (IOPS). IOPS from them could be used to compare IOPS optimum capacity resulted from SQLIO test.
For daily storage monitoring, I recommend you monitor by using these 2 performance counters here :
- %Disk read time, %Disk time, %Disk write time and %Disk idle time
- Avg. Disk Bytes/Read, Avg. Disk Bytes/Transfer and Avg. Disk Bytes/Write.