sql数据库性能指标

of the SQL Server performance metrics series, we presented the most important and useful disk performance metrics. Now, we’ll show other important disk performance measures 中，我们介绍了最重要和最有用的磁盘性能指标。 现在，我们将展示其他重要的磁盘性能指标

当前磁盘队列长度 (Current Disk Queue Length)

“Indicates the number of disk requests that are currently waiting as well as requests currently being serviced. Subject to wide variations unless the workload has achieved a steady state and you have collected a sufficient number of samples to establish a pattern.” [1]

“表示当前正在等待的磁盘请求数以及当前正在处理的请求数。 除非工作负载已达到稳定状态并且您已经收集了足够数量的样本以建立模式，否则可能会有很大差异。” [1]

The metric shows how many I/O operations are waiting to be written to or read from the hard drive and how many are currently processed. If the hard drive is not available, these operations are queued and will be processed when disk becomes available. The whole disk subsystem has a single queue

该度量标准显示有多少I / O操作正在等待写入硬盘或从中读取，以及当前正在处理多少。 如果硬盘驱动器不可用，这些操作将排队，并且在磁盘可用时将进行处理。 整个磁盘子系统只有一个队列

The Current Disk Queue Length metric in Windows Performance Monitor is available for both physical and logical disk. In some earlier versions of Performance Monitor, this counter was named Disk Queue Length

Windows Performance Monitor中的“ 当前磁盘队列长度”度量标准可用于物理磁盘和逻辑磁盘。 在性能监视器的某些早期版本中，此计数器名为“ 磁盘队列长度”。

The Current Disk Queue Length value should be less than 2 per disk spindle. Note that this is not per logical, but per physical disk. If larger, this indicates a potential disk bottleneck, so further investigation and monitoring other disk metrics is recommended. Start with monitoring %Disk Time (explained below). Frequent peaks should also be investigated

每个磁盘主轴的“ 当前磁盘队列长度”值应小于2。 请注意，这不是每个逻辑磁盘，而是每个物理磁盘。 如果更大，则表明存在潜在的磁盘瓶颈，因此建议进一步调查和监视其他磁盘指标。 从监视％Disk Time开始 （如下所述）。 还应调查频繁的峰

Disk array systems such as RAID or SAN have a large number of disks and controllers, which makes queues on such systems shorter. Because the metric doesn’t indicate queuing per disk, but for the whole array, some DBAs consider that monitoring Current Disk Queue Length on disk arrays is not needed

RAID或SAN等磁盘阵列系统具有大量磁盘和控制器，这使得此类系统上的队列更短。 因为该度量标准不表示每个磁盘的排队，而是整个阵列的排队，所以某些DBA认为不需要监视磁盘阵列上的当前磁盘队列长度 。

Another scenario where Current Disk Queue Length can be misleading is when data is stored in the disk cache. It will be reported as being queued for writing and thus the Current Disk Queue Length value will be higher than actual

当前磁盘队列长度可能会引起误解的另一种情况是何时将数据存储在磁盘缓存中。 将报告它正在排队等待写入，因此“ 当前磁盘队列长度”值将高于实际值。

平均磁盘队列长度 (Average Disk Queue Length)

The Average Disk Queue Length metric shows the information similar to Current Disk Queue Length, only the value is not current but average over a specific time period. The threshold is the same as for the previous metric – up to 2 per disk. For disk systems, the recommended value is less than 2 per individual disk drive in an array. For example, in a 6 disk array the Current Disk Queue Length value of 12 means that the queue is 2 per disk

“ 平均磁盘队列长度”度量标准显示类似于“ 当前磁盘队列长度”的信息 ，只是该值不是当前值，而是特定时间段内的平均值。 该阈值与先前的指标相同-每个磁盘最多2个。 对于磁盘系统，阵列中每个磁盘驱动器的建议值均小于2。 例如，在6个磁盘阵列中， 当前磁盘队列长度值为12表示每个磁盘队列为2

There are two more metrics similar to Average Disk Queue Length – Average Disk Read Queue Length and Average Disk Write Queue Length. As their names indicate – they show the average queue length for operations waiting for disk to be read or written

还有两个类似于“ 平均磁盘队列长度”的度量标准–“平均磁盘读取队列长度”和“ 平均磁盘写入队列长度” 。 顾名思义，它们显示等待磁盘读写操作的平均队列长度

％磁盘时间 (%Disk Time)

“This counter indicates a disk problem, but must be observed in conjunction with the Current Disk Queue Length counter to be truly informative. Recall also that the disk could be a bottleneck prior to the %Disk Time reaching 100%” [2]

“此计数器表示磁盘有问题，但必须与“当前磁盘队列长度”计数器一起观察才能真正提供信息。 还记得在％Disk Time达到100％之前磁盘可能是瓶颈” [2]

The %Disk Time metric indicates how busy the disk is servicing read and write requests, but as stated above, it’s not a clear indication of a problem, as its values can be normal while there’s a serious disk performance issue. Its value is the Average Disk Queue Length value represented in percents (i.e. multiplied by 100). If Average Disk Queue Length is 1, %Disk Time is 100%

％Disk Time指标指示磁盘正在处理读取和写入请求的繁忙程度，但是如上所述，这并不是一个明显的问题迹象，因为在出​​现严重的磁盘性能问题时，其值可能是正常的。 它的值是“ 平均磁盘队列长度”值，以百分比表示（即乘以100）。 如果平均磁盘队列长度为1，则％磁盘时间为100％

What can be confusing is that %Disk Time values can be over 100%, which isn’t logical. This happens if the Average Disk Queue Length value is greater than 1. If Average Disk Queue Length is 3, %Disk Time is 300%, which doesn’t mean that processes are using 3 times more disk time than available, nor that there is a bottleneck

令人困惑的是％Disk Time值可能超过100％，这是不合逻辑的。 如果“ 平均磁盘队列长度”值大于1，则会发生这种情况。如果“ 平均磁盘队列长度”为3，则“ 磁盘时间百分比”为300％，这并不意味着进程使用的磁盘时间是可用磁盘时间的3倍，也不意味着瓶颈

If you have a hard disk array, the total disk time for all disks is shown, without the indication of how many disks are available and what disk is having the highest %Disk Time. For example, %Disk Time equal to 500% might indicate good performance (in case you have 6 disks), or extremely bad (in case you have only 1 disk). You cannot tell without knowing the machine hardware

如果您有硬盘阵列，则会显示所有磁盘的总磁盘时间，而不会指出有多少可用磁盘以及哪个磁盘具有最高％Disk Time 。 例如， ％Disk Time等于500％可能表示性能良好（如果您有6个磁盘），或者非常糟糕（如果您只有1个磁盘）。 不知道机器硬件就无法分辨

As this counter can be misleading, some DBAs don’t use it as there are other more straightforward and indicative metrics that show disk performance

由于该计数器可能会产生误导，因此某些DBA不会使用它，因为还有其他更直接和指示性的指标可以显示磁盘性能

If the value is higher than 90% per disk, additional investigation is needed. First, check the Current Disk Queue Length value. If it’s higher than the threshold (2 per physical disk), monitor if the high values occur frequently. If the machine is not used only for SQL Server, other resource-intensive applications might cause disk bottlenecks, so SQL Server performance will be suffering. If this is the case, consider moving these applications to another machine and using a dedicated machine for SQL Server only

如果该值高于每个磁盘的90％，则需要进行其他调查。 首先，检查“ 当前磁盘队列长度”值。 如果它高于阈值（每个物理磁盘2个），请监视高值是否频繁出现。 如果该计算机不仅用于SQL Server，其他资源密集型应用程序可能会导致磁盘瓶颈，因此SQL Server性能将受到影响。 如果是这种情况，请考虑将这些应用程序移动到另一台计算机上，并仅将专用计算机用于SQL Server。

If this is not the case, or cannot be done, consider moving some of the files to another disk or machine – archive databases, database and transaction log backups, using a faster disk, or adding additional disks to an array

如果不是这种情况，或者无法完成，请考虑将某些文件移动到另一个磁盘或计算机上–归档数据库，数据库和事务日志备份，使用更快的磁盘或将其他磁盘添加到阵列

％Disk读取时间和％Disk写入时间 (%Disk Read Time and the %Disk Write Time)

The %Disk Read Time and %Disk Write Time metrics are similar to %Disk Time, just showing the operations read from or written to disk, respectively. They are actually the Average Disk Read Queue Length and Average Disk Write Queue Length values presented in percents. The values these metrics show can be equally misleading as %Disk Time

％Disk Read Time和％Disk Write Time指标类似于％Disk Time ，仅分别显示了从磁盘读取或写入磁盘的操作。 它们实际上是以百分比表示的“ 平均磁盘读取队列长度”和“ 平均磁盘写入队列长度”值。 这些指标显示的值可能会误导为％Disk Time

On a three – disk array system, if one disk reads 50% of the time (%Disk Read Time =50%), the other one reads 85% of the time, and the third one is idle, %Disk Read Time is 135% and Average Disk Read Queue Length 1.35. At a first glance, %Disk Read Time equal to 135% looks like a problem, but it’s not. It doesn’t mean that disks are busy 135% of the time. To get a real value, you should divide the value with the number of disks and you’ll get 136%/3 = 45%, which indicates normal performance

在三磁盘阵列系统上，如果一个磁盘读取时间的百分比为50％（ ％Disk Read Time = 50％），则另一个磁盘读取的时间百分比为85％，而第三个磁盘处于空闲状态， ％Disk Read Time为135 ％和平均磁盘读取队列长度 1.35。 乍一看， ％Disk Read Time等于135％看起来是个问题，但不是问题。 这并不意味着磁盘有135％的时间处于繁忙状态。 要获得实际值，您应该将该值除以磁盘数量，将得到136％/ 3 = 45％，这表明性能正常

％空闲时间 (%Idle Time)

The disk is idle when it’s not processing read and write requests

磁盘不处理读写请求时处于空闲状态

“This measures the percentage of time the disk was idle during the sample interval. If this counter falls below 20 percent, the disk system is saturated. You may consider replacing the current disk system with a faster disk system.” [3]

“这衡量了在采样间隔内磁盘空闲时间的百分比。 如果此计数器降到20％以下，则磁盘系统已饱和。 您可以考虑用更快的磁盘系统替换当前的磁盘系统。” [3]

If the value is lower than 20%, disk is not able to service all read and write requests in a timely fashion. Before opting for disk replacement, check whether it’s possible to remove some applications to another machine

如果该值低于20％，则磁盘将无法及时处理所有读取和写入请求。 选择更换磁盘之前，请检查是否可以将某些应用程序删除到另一台计算机上

％可用空间 (%Free Space)

Besides Windows Performance Monitor, this metric is available in Windows Explorer in the computer and disk Properties tabs. While Performance Monitor shows the percentage of available free disk space, Windows Explorer shows the amount in GB

除了Windows Performance Monitor，此度量标准在Windows资源管理器的计算机和磁盘“ 属性”选项卡中可用。 性能监视器显示可用磁盘空间的百分比，而Windows资源管理器显示以GB为单位的数量

“This measures the percentage of free space on the selected logical disk drive. Take note if this falls below 15 percent, as you risk running out of free space for the OS to store critical files. One obvious solution here is to add more disk space.” [3]

“这将测量所选逻辑磁盘驱动器上的可用空间百分比。 请注意，如果这下降到15％以下，则可能会耗尽操作系统存储关键文件的可用空间。 一种明显的解决方案是增加更多的磁盘空间。” [3]

If the value shows sudden peaks without obvious reasons, further investigation is required

如果该值显示突然的峰值而没有明显原因，则需要进一步调查

Unlike most of the memory and processor SQL Server performance metrics, disk metrics can be quite deceptive. They might not clearly indicate a performance problem; their values might be OK, when actually there is a serious disk issue, while their strangely high values might show normal performance, as they show values for an array of disks. Then it comes to array metrics, hardware configuration knowledge is necessary to read them correctly. Despite these disk metrics downsides, they are necessary for SQL Server performance troubleshooting

与大多数内存和处理器SQL Server性能指标不同，磁盘指标可能具有欺骗性。 它们可能无法清楚地表明性能问题； 当实际上存在严重的磁盘问题时，它们的值可能还可以，而当它们显示磁盘阵列的值时，它们的高值可能会表现出异常的正常性能。 然后是阵列指标，必须具备硬件配置知识才能正确读取它们。 尽管有这些磁盘指标的缺点，但它们对于SQL Server性能故障排除是必需的

翻译自:

sql数据库性能指标