If the Average Disk sec/Read and Average Disk sec/Write values are constantly above the recommended values, it’s an indication of a disk bottleneck and additional analysis is required In case of cached writes, the values are very different – values higher than 4 milliseconds indicate poor performance, while the values less than 1 milliseconds indicate the best performance Value (ms) The recommended values for non-cached writes are the same as for Average Disk sec/Read. Usually, the read and write speed on a disk are different. Maximum peaks during excessive I/O operations can be up to 25 milliseconds, but values constantly higher than 20 milliseconds indicate poor performance Average Disk sec/WriteĪverage Disk sec/Write is another useful disk performance metric that shows the average time in seconds needed to write data to disk The recommended Average Disk sec/Read value is below 8ms Value (ms) The time added to one disk rotation is the queuing time and the time needed for data transit across the I/O bus Average Disk sec/Read for that disk should be a multiple of 16 milliseconds. For example, a disk that makes 3,600 round per minute needs 60s/3600 = 0.016 seconds, i.e. The rotational speed of the hard drive will be the predominant factor in the value with the delays imposed by the controller card and support bus system.” Īverage Disk sec/Read is proportional to time needed for one disk rotation. This does not negate the rule that the entire system is being observed. On less-complex disk subsystems involving controllers that do not have intelligent management of the I/O, this value is a multiple of the disk’s rotation per minute. “The value for this counter is generally the number of seconds it takes to do each read. The shorter the time needed to read or write data, the faster the system Both metrics can be tracked on logical and physical disk levels and show disk latency. The Average Disk sec/Read metric, along with Average Disk sec/Read (presented next), is one of the most important disk performance metrics. The SQL Server activities that require disk access are creating database and transaction log backups and saving them to disk, import/export processes, jobs that read or write large amounts of data from/to disk, etc. To troubleshoot SQL Server disk issues, besides total disk I/O activity, it’s recommended to monitor and detect disk activity made by SQL ServerĮxcessive disk using by various applications can cause SQL Server performance degradation, as SQL Server might not be the master of disk resources and would have to wait for disk reads and writes. Problems with disk I/O operations are manifested through slow response times, operation time outs, and system bottlenecks SQL Server uses I/O calls to perform reads and writes on a disk, it defines and manages requests for reading and writing the data, while the operating system actually performs the I/O operations. This is where logical disk metrics are useful, as they show more granular results and help determining effect of SQL Server or any other application on disk performance Some physical disk metrics might not be sufficient for deeper investigation and troubleshooting if you have more than one logical partition on a disk. The metrics in both groups are the same, the only difference is whether they show the performance for a single partition, or for the entire disk A Logical disk is a disk partition, while a physical disk is the complete physical disk with all partitions created on it. Most of these metrics are available in Windows Performance Monitor, where they are divided into 2 groups – Physical Disk and Logical Disk metrics. Then, compare the current metric values to baselines For SQL Server disk performance monitoring, it’s recommended to monitor the metrics for a while, determine the trend, and set a baseline for normal operation. That’s why it’s necessary to understand the cause and effect of each metricĭisk metrics are not related only to disk itself, but to the whole disk subsystem which includes disk, the disk controller card and the I/O disk system bus. a disk issue can cause processor bottlenecks. Sometimes a metric from one category can be masked by other events and be misleading – e.g. Besides memory and processor metrics, equally important are SQL Server disk metrics. These metrics indicate system and SQL Server performance, and are useful for troubleshooting performance issues and bottlenecks. So far, we have presented the most important memory and processor metrics.
0 Comments
Leave a Reply. |