sql数据库性能指标
memory and 内存和 processor metrics. These metrics indicate system and SQL Server performance, and are useful for troubleshooting performance issues and bottlenecks. Besides memory and processor metrics, equally important are SQL Server disk metrics. Sometimes a metric from one category can be masked by other events and be misleading – e.g. a disk issue can cause processor bottlenecks. That’s why it’s necessary to understand the cause and effect of each metric 处理器指标。 这些指标指示系统和SQL Server的性能，对于解决性能问题和瓶颈很有用。 除了内存和处理器指标外，SQL Server磁盘指标同样重要。 有时，来自一个类别的指标可能会被其他事件掩盖并产生误导作用-例如，磁盘问题可能会导致处理器瓶颈。 这就是为什么有必要了解每个指标的因果关系的原因Disk 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. 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. Then, compare the current metric values to baselines
磁盘指标不仅与磁盘本身有关，而且与整个磁盘子系统有关，包括磁盘，磁盘控制器卡和I / O磁盘系统总线。 对于SQL Server磁盘性能监视，建议一段时间内监视指标，确定趋势并为正常操作设置基准。 然后，将当前指标值与基线进行比较
Most of these metrics are available in Windows Performance Monitor, where they are divided into 2 groups – Physical Disk and Logical Disk metrics. A Logical disk is a disk partition, while a physical disk is the complete physical disk with all partitions created on it. 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
这些度量标准中的大多数都可在Windows Performance Monitor中使用，它们分为2组-物理磁盘和逻辑磁盘度量标准。 逻辑磁盘是磁盘分区，而物理磁盘是在其上创建了所有分区的完整物理磁盘。 两组中的指标都相同，唯一的不同是它们显示的是单个分区还是整个磁盘的性能
Some physical disk metrics might not be sufficient for deeper investigation and troubleshooting if you have more than one logical partition on a disk. 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
如果磁盘上有多个逻辑分区，则某些物理磁盘指标可能不足以进行更深入的调查和故障排除。 这是逻辑磁盘指标有用的地方，因为它们显示了更精细的结果并有助于确定SQL Server或任何其他应用程序对磁盘性能的影响
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. Problems with disk I/O operations are manifested through slow response times, operation time outs, and system bottlenecks
SQL Server使用I / O调用在磁盘上执行读取和写入操作，它定义和管理读取和写入数据的请求，而操作系统实际上执行I / O操作。 磁盘I / O操作的问题表现为响应速度慢，操作超时和系统瓶颈
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
若要解决SQL Server磁盘问题，除了总的磁盘I / O活动外，建议监视和检测SQL Server进行的磁盘活动
Excessive 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. 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.
各种应用程序过度使用磁盘会导致SQL Server性能下降，因为SQL Server可能不是磁盘资源的主控者，因此必须等待磁盘读写。 需要磁盘访问SQL Server活动是创建数据库和事务日志备份，并将它们保存到磁盘，导入/导出过程，从磁盘读取或向磁盘写入大量数据的作业等。
平均磁盘秒数/读取 (Average Disk sec/Read)
The Average Disk sec/Read metric, along with Average Disk sec/Read (presented next), is one of the most important disk performance metrics. Both metrics can be tracked on logical and physical disk levels and show disk latency. The shorter the time needed to read or write data, the faster the system
平均磁盘秒/读取量度以及平均磁盘秒/读取量 （如下所示）是最重要的磁盘性能量度之一。 可以在逻辑和物理磁盘级别上跟踪这两个指标，并显示磁盘延迟。 读取或写入数据所需的时间越短，系统越快
“The value for this counter is generally the number of seconds it takes to do each read. 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. This does not negate the rule that the entire system is being observed. 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.” [1]
“该计数器的值通常是每次读取所花费的秒数。 在涉及没有对I / O进行智能管理的控制器的不太复杂的磁盘子系统上，此值是每分钟磁盘旋转数的倍数。 这不会否定要观察整个系统的规则。 硬盘驱动器的旋转速度将成为该值的主要因素，而控制器卡和支持总线系统会产生延迟。” [1]
Average Disk sec/Read is proportional to time needed for one disk rotation. For example, a disk that makes 3,600 round per minute needs 60s/3600 = 0.016 seconds, i.e. 16 milliseconds to make one rotation. Average Disk sec/Read for that disk should be a multiple of 16 milliseconds. The time added to one disk rotation is the queuing time and the time needed for data transit across the I/O bus
平均磁盘秒/读取与旋转一个磁盘所需的时间成比例。 例如，每分钟旋转3,600次的磁盘需要60s / 3600 = 0.016秒，即旋转16毫秒。 该磁盘的平均磁盘秒数/读取数应为16毫秒的倍数。 磁盘旋转一圈所花费的时间是排队时间和通过I / O总线传输数据所需的时间
The recommended Average Disk sec/Read value is below 8ms
建议的平均磁盘秒/读取值低于8ms
| Value (ms) | Performance |
| < 8 | Excellent |
| 8 – 12 | OK |
| 12 – 20 | Fair |
| > 20 | Bad |
| 值（毫秒） | 性能 |
| <8 | 优秀的 |
| 8 – 12 | 好 |
| 12 – 20 | 公平 |
| > 20 | 坏 |
Maximum peaks during excessive I/O operations can be up to 25 milliseconds, but values constantly higher than 20 milliseconds indicate poor performance
过多的I / O操作期间的最大峰值可能高达25毫秒，但是值始终高于20毫秒表示性能不佳
平均磁盘秒/写入 (Average Disk sec/Write)
Average Disk sec/Write is another useful disk performance metric that shows the average time in seconds needed to write data to disk
平均磁盘秒数/写入数是另一个有用的磁盘性能指标，它显示将数据写入磁盘所需的平均时间（以秒为单位）
Usually, the read and write speed on a disk are different. The recommended values for non-cached writes are the same as for Average Disk sec/Read. 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
通常，磁盘上的读写速度是不同的。 非高速缓存写入的建议值与“ 平均磁盘秒/读取 ”的建议值相同。 如果是缓存写操作，则这些值将有很大的不同–高于4毫秒的值表示性能较差，而低于1毫秒的值表示最佳性能
| Value (ms) | Performance |
| < 1 | Excellent |
| 1 – 2 | OK |
| 2 – 4 | Fair |
| > 4 | Bad |
| 值（毫秒） | 性能 |
| <1 | 优秀的 |
| 1 – 2 | 好 |
| 2 – 4 | 公平 |
| > 4 | 坏 |
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
如果“ 平均磁盘秒/读取”和“ 平均磁盘秒/写入”值始终高于建议值，则表明存在磁盘瓶颈，需要进行其他分析
“After you have found the disks with high levels of read/write activity, look at the read-specific and write-specific counters (for example, Logical Disk: Disk Write Bytes/sec) for the type of disk activity that is causing the load on each logical volume.” [2]
“找到具有较高读写活动级别的磁盘后，请查看特定于读取和特定于写入的计数器（例如，逻辑磁盘：磁盘写入字节/秒），以了解导致磁盘活动的磁盘活动类型。在每个逻辑卷上加载。” [2]
If the Average Disk sec/Read and Average Disk sec/Write values are high for all or almost all disks, the problem is most probably caused by disk communication mediums. If only a specific disk shows poor performance, the problem is most probably in disk itself
如果所有或几乎所有磁盘的“ 平均磁盘秒/读取”和“ 平均磁盘秒/写入”值都很高，则问题很可能是由磁盘通信介质引起的。 如果仅特定磁盘显示了较差的性能，则问题很可能出在磁盘本身上
Monitoring both values can help you determine if reconfiguration of disk controller cache is needed. If for example, the Average Disk sec/Read value is significantly higher than Average Disk sec/Write, you can consider cache optimization for reading
监视这两个值可以帮助您确定是否需要重新配置磁盘控制器缓存。 例如，如果“ 平均磁盘秒/读取”值明显高于“ 平均磁盘秒/写入” ，则可以考虑对读取进行缓存优化
平均磁盘秒数/传输 (Average Disk sec/Transfer)
The Average Disk sec/Transfer metric shows disk efficiency as the average time needed for each read and write
平均磁盘秒数/传输数指标将磁盘效率表示为每次读取和写入所需的平均时间
“Measures the average time of each data transfer, regardless of the number of bytes read or written. Shows the total time of the read or write, from the moment it leaves the Diskperf.sys driver to the moment it is complete
A high value for this counter might mean that the system is retrying requests due to lengthy queuing or, less commonly, disk failures. “[3]
“ 测量每次数据传输的平均时间，与读取或写入的字节数无关。 显示从磁盘退出Diskperf.sys驱动程序到完成为止的总读写时间。
此计数器的值很高，可能意味着由于排队时间较长或磁盘故障（较不常见），系统正在重试请求。 “ [3]
The recommended value is the same as for the previous two metrics
推荐值与前两个指标相同
There’s no need to monitor this metric along with Average Disk sec/Read and Average Disk sec/Write, as the latter two are combined in Average Disk sec/Transfer. But if you’re monitoring Average Disk sec/Transfer and its values are higher than recommended, monitoring Average Disk sec/Read and Average Disk sec/Write is the first step in further troubleshooting
无需与“ 平均磁盘秒/读取”和“ 平均磁盘秒/写入”一起监视此度量标准，因为后两者在“ 平均磁盘秒/传输”中组合在一起。 但是，如果要监视“ 平均磁盘秒/传输”并且其值高于建议值，则监视“ 平均磁盘秒/读取”和“ 平均磁盘秒/写入”是进行进一步故障排除的第一步
磁盘读取/秒和磁盘写入/秒 (Disk Reads/sec and Disk Writes/sec)
The Disk Reads/sec and Disk Writes/sec metrics show the rate of read and write operations on disk, respectively
“ 磁盘读取/秒”和“ 磁盘写入/秒”指标分别显示磁盘上的读取和写入操作的速率
The metric that shows the combined value of these two is Disk Transfers/sec, it’s the total number of all I/O disk requests generated in a second
显示这两项的总和的指标是Disk Transfers / sec ，它是在一秒钟内生成的所有I / O磁盘请求的总数
If the values are low, they indicate slow disk I/O operation processing and you should check processor usage parameters and disk-expensive queries
如果值太低，则表明磁盘I / O操作处理缓慢，您应检查处理器使用率参数和磁盘昂贵的查询
There is no specific threshold, as it depends on disk specification and your server configuration. For an array system, the values shown are for all disks. With that said, it’s recommended to monitor these metrics for a while and to determine trends and set a baseline. Any unexpected peaks should be investigated
没有具体的阈值，因为它取决于磁盘规格和服务器配置。 对于阵列系统，显示的值适用于所有磁盘。 话虽如此，建议您一段时间内监视这些指标并确定趋势并设置基准。 任何意外的峰值都应进行调查
In this part of the SQL Server performance metrics series, we presented the most important disk performance metrics. All metrics show disk latency and if the latency is too high, the final solution is upgrading the disk subsystem, or adding more disks
在SQL Server性能指标系列的这一部分中，我们介绍了最重要的磁盘性能指标。 所有指标均显示磁盘延迟，如果延迟过高，则最终解决方案是升级磁盘子系统或添加更多磁盘
sql数据库性能指标
644
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
