sql数据库性能指标_SQL Server网络性能指标–最重要的指标

sql数据库性能指标

The network parameters is the metric category often neglected when troubleshooting SQL Server issues. Sometimes, SQL Server problems are caused by network congestion. It doesn’t mean that the cause of the bottlenecks originate on SQL Server

在对SQL Server问题进行故障排除时，网络参数是通常被忽略的度量标准类别。有时，SQL Server问题是由网络拥塞引起的。这并不意味着瓶颈的原因起源于SQL Server

All network performance metrics are easiest to read and understand when there are no applications except SQL Server running on the server, as nailing down the application that causes excessive network traffics is simple. Then, you should search for SQL queries, clients, and events that transfer large amount of data over network

当除服务器上运行SQL Server外没有其他应用程序时，所有网络性能指标最容易阅读和理解，因为确定导致过多网络流量的应用程序很简单。然后，您应该搜索通过网络传输大量数据SQL查询，客户端和事件

接收的网络字节数/秒 (Network Bytes Received/sec)

The Network Bytes Received/sec counter shows the byte rate at which information is received over each network adapter. The bytes used for data packet framing are also counted and included in the value

“ 网络字节接收数/秒”计数器显示通过每个网络适配器接收信息的字节率。用于数据包成帧的字节也被计数并包含在该值中

The counter shows “how many bytes you get from the NIC. This is a measure of the inbound traffic” [1]

计数器显示“您从NIC获得多少字节。 这是对入站流量的度量” [1]

You can use this counter to calculate the incoming data rate based on the total available network bandwidth, as this information is easier to understand and identify potential network clogging

您可以使用此计数器基于总可用网络带宽来计算传入数据速率，因为此信息更易于理解和识别潜在的网络阻塞

There is no specific threshold value. However, have in mind that Network Bytes Received/sec is a component of the Total bytes/sec counter, which should be less than 50% of total network bandwidth

没有具体的阈值。但是，请记住，“ 每秒接收的网络字节数”是“ 总字节数/秒”计数器的组成部分，应小于总网络带宽的50％

It’s recommended to watch this counter overtime and determine trends. Any unexpected peak in network activity should be investigated and the origin of the massive network traffic should be identified

建议您注意此计数器的超时情况并确定趋势。应调查网络活动中任何意外的高峰，并应确定大量网络流量的来源

网络字节发送/秒 (Network Bytes Sent/sec)

Similar to the Network Bytes Received/sec counter, the Network Bytes Sent/sec counter shows the rate at which bytes are sent over each network adapter. Again, it’s useful to calculate the outgoing data rate as a percentage of the total network bandwidth

与“已接收的网络字节数/秒”计数器类似，“已发送的网络字节数/秒”计数器显示通过每个网络适配器发送字节的速率。同样，将传出数据速率计算为总网络带宽的百分比非常有用

“This is how many bytes of data are sent to the NIC. This is a raw measure of throughput for the network interface. We are really measuring the information sent to the interface which is the lowest point we can measure. If you have multiple NIC, you will see multiple instances of this particular counter.” [1]

这就是发送到NIC的数据字节数。 这是网络接口吞吐量的原始度量。 我们实际上正在测量发送到接口的信息，这是我们可以测量的最低点。 如果您有多个NIC，则将看到此特定计数器的多个实例。” [1]

The same as with Network Bytes Received/sec, there is no specific threshold, but you should have in mind the Total bytes/sec value

与“ 每秒接收的网络字节数 ”相同，没有特定的阈值，但是您应该记住“ 总字节数/秒”值

网络字节总数/秒 (Network Bytes Total/sec)

As expected, the sum of Bytes Received/sec and Bytes Sent/sec is equal to the Bytes Total/sec value. It is the byte rate at which data is received and sent over each network adapter. Framing bytes are included

正如预期的那样，“ 接收的字节数/秒”和“ 发送的字节数/秒”之和等于“ 字节总数/秒”值。它是通过每个网络适配器接收和发送数据的字节速率。包含帧字节

“This is simply a combination of the other two counters. This will tell you overall how much information is going in and out of the interface. Typically, you can use this to get a general feel, but will want to look at the Bytes Sent/sec and the Bytes Received/sec for a more exact detail of the type of traffic.” [1]

“这只是其他两个柜台的组合。 这将总体上告诉您有多少信息进出接口。 通常，您可以使用它来获得一般的感觉，但希望查看发送的字节数/秒和接收的字节数/秒，以更详细地了解流量类型。” [1]

When investigating high Bytes Total/sec values, check the %Disk Time counter for the physical disk and %Processor Time values. If the latter two are normal, this is an indication of a capacity problem that can be solved by network interface configuration or adding an additional network adapter. Also, network re-configuration and creating subnets can solve the problem

调查高字节总数/秒值时，请检查物理磁盘的％Disk Time计数器和％Processor Time值。如果后两个正常，则表明存在容量问题，可以通过配置网络接口或添加其他网络适配器来解决。此外，网络重新配置和创建子网也可以解决问题

当前网络带宽 (Current Network Bandwidth)

The network bandwidth shows how much data can be transferred through a network interface over time. Some other resources define it as the maximum rate of a network data transfer, or network capacity. The network bandwidth depends on the network infrastructure. What you can control and configure is the amount of network bandwidth used by your servers

网络带宽显示了一段时间内可以通过网络接口传输多少数据。其他一些资源将其定义为网络数据传输的最大速率或网络容量。网络带宽取决于网络基础结构。您可以控制和配置的是服务器使用的网络带宽量

The Current Network Bandwidth metric “Shows an estimate of the current bandwidth of the network interface in bits per second (BPS). For interfaces that do not vary in bandwidth or for those where no accurate estimation can be made, this value is the nominal bandwidth.” [2]

“ 当前网络带宽”度量标准“以每秒比特数（BPS）显示网络接口当前带宽的估计值”。 对于带宽没有变化的接口或无法进行准确估算的接口，该值为名义带宽。” [2]

A network with a high bandwidth is a prerequisite for good SQL Server performance over network

具有高带宽的网络是通过网络获得良好SQL Server性能的先决条件

For performance monitoring, compare the Current Network Bandwidth value to Bytes Total/sec. The Bytes Total/sec and Current Network Bandwidth ratio is also called Network utilization

为了进行性能监视，请将“ 当前网络带宽”值与“ 总字节数/秒”进行比较 。 字节总数/秒和当前网络带宽比率也称为网络利用率

The Bytes Total/sec value for all servers on a network should be less than 50% of the total network bandwidth. If constantly higher, additional investigation is required. The first step is to determine the applications that are saturating the network. Have in mind that some operations, such as creating SQL Server database backups on a remote storage, use a lot of network bandwidth

网络上所有服务器的“ 字节总数/秒”值应小于网络总带宽的50％。如果持续升高，则需要进行其他调查。第一步是确定使网络饱和的应用程序。请记住，某些操作（例如在远程存储上创建SQL Server数据库备份）会占用大量网络带宽

The ratio values higher than 80% indicate very high network utilization that should be attended immediately. If the ratio is close to 100%, it means that you’re using almost maximum network capacity, and that additional traffic will cause bottlenecks, which will reflect in delays. This situation can be solved by increasing the bandwidth (which is sometimes not easy to achieve, or not possible at all), or segmenting the network

比率值高于80％表示网络利用率非常高，应立即注意。如果比率接近100％，则意味着您正在使用几乎最大的网络容量，并且额外的流量将导致瓶颈，这将反映为延迟。可以通过增加带宽（有时不容易实现或根本不可能）或分段网络来解决这种情况。

To see real-time values of network performance metrics listed above – Network Bytes Received/sec, Network Bytes Sent/sec, and Network Bytes Total/sec, use Windows Resource Monitor

若要查看上面列出的网络性能指标的实时值- 接收的网络字节/秒，发送的网络字节/秒和总计网络字节/秒 ，请使用Windows资源监视器

Open Windows Task Manager (press Ctrl+Alt+Delete)
打开Windows任务管理器 （按Ctrl + Alt + Delete）
Open the Performance tab
打开性能选项卡
Click Open Resource Monitor at the bottom
单击底部的“ 打开资源监视器” 。
Open the Network tab
打开网络标签

It consists of four sections – Processes with Network Activity, Network Activity, TCP Connections, and Listening Ports

它包括四个部分– 具有网络活动 ， 网络活动 ， TCP连接和侦听端口的 进程

Selecting a process in the first section displays its details in the other three

在第一部分中选择一个过程会在其他三个部分中显示其详细信息

The TCP Connections and Listening Ports sections show information useful for deeper investigation and troubleshooting

“ TCP连接和侦听端口”部分显示的信息对于深入调查和故障排除很有用

Besides the network performance metrics listed above, there are many more. The metrics available in Windows Performance Monitor are Processor : % DPC Time, Processor : DPCs queued/sec, Network Segment : %Broadcasts, Network Segment : %Multicasts, TCP : Segments Sent/sec, TCP : Segments Received/sec, TCP : Segments/sec, TCP : Segments Retransmitted/sec, TCP : Connection Failures, TCP : Connections Reset, and TCP : Connections Established. However, these metrics are rarely used for troubleshooting SQL Server performance issues

除了上面列出的网络性能指标之外，还有更多其他指标。 Windows Performance Monitor中可用的度量标准是：处理器：％DPC时间；处理器：DPC排队/秒；网络段：％广播；网络段：％组播； TCP：发送的段/秒； TCP：接收的段/秒； TCP：段/ sec，TCP：重新传输的段/秒，TCP：连接失败，TCP：连接重置和TCP：建立连接。 但是，这些指标很少用于解决SQL Server性能问题

To solve SQL Server network bottlenecks, start with optimizing your workload. Consider optimizing and/or reducing network traffic between your client application and the SQL Server instance. It requires design changes, such as using stored procedures instead of queries, reusing execution plans, calling smaller data sets where applicable, etc.

要解决SQL Server网络瓶颈，首先要优化您的工作量。考虑优化和/或减少客户端应用程序和SQL Server实例之间的网络流量。它需要进行设计更改，例如使用存储过程代替查询，重用执行计划，在适用时调用较小的数据集等。

If this doesn’t provide adequate performance improvement, add network adapters and re-configure network settings

如果这样做不能充分改善性能，请添加网络适配器并重新配置网络设置

翻译自: https://www.sqlshack.com/sql-server-network-performance-metrics-important-metrics/