Before you can use the network performance counters, the Network Monitor Agent service must be installed on your server. After installing it, you will have to reboot. Also, don’t forget to rerun the Windows Server service pack to update the files added during the installation process.
*****
One of the best ways to monitor if you have a network bottleneck is to watch the Network Interface Object: Bytes Total/Sec counter. This counter measures the number of bytes that are being sent back and forth between your server and the network. This includes both SQL Server and non-SQL Server network traffic. Assuming your server is a dedicated SQL Server, the vast majority of the traffic measured by this counter should be from SQL Server.
There is no hard and fast “correct” number for this counter as it measures the actual traffic. To help you decide if your server has a network bottleneck, one way to use this number is to compare it with the maximum traffic supported by the network connection your server is using. Also, this is another important counter to watch over time. It is important to know if your network traffic is increasing regularly. If it is, then you can use this information to help you plan for future hardware needs.
*****
If you think that you have a network bottleneck, it is easy to check using the Network Segment Object: % Network Utilization counter. This counter provides you with what percentage of the bandwidth is being used by the network connection your server is using. This is not the amount of bandwidth being sent to and from your server, but the total bandwidth being used on the connection the network card is attached to.
This connection could be of many different types, including a shared hub or a switched port running at half-duplex or full-duplex. The connection might be 10Mbp, 100Mbp, or even 1Gbp. Given this, the results you receive from the counter must be interpreted in the light of which type of connection you have. Ideally, you will want a network connection to its own dedicated switch port for maximum performance.
*****
If you want to find out how much data is being sent back and forth from your server to the network, use the Server Object: Bytes Received/sec and the Server Object: Bytes Transmitted/sec. These counters will help you found out how busy your actual server is over the network, and are good counters to watch over time.
*****
One way to help identify if you have exceed the NIC capacity of your SQL Server is to watch the SQLServer: SQL Statistics: Batch Requests/Sec counter. This counter measures the amount of SQL batches per second that SQL Server is being given. Generally speaking, a single 100Mbs NIC can handle about 3000/second. If your system consistently exceeds this amount, then you need to consider additional network cards or a faster network card.
*****
One way to help determine if your current NIC(s) has become a bottleneck for your SQL Server is to perform the following calculation, which is based on two separate Performance Monitor counters.
Network Interface Object: Bytes Total/Sec Counter divided by Network Interface Object: Current Bandwidth
If you determine the average for both of these counters for a typical activity time period, and then divide them, the resulting value should be below a value of “.6”. If it is not, then it is very likely that your SQL Server is experiencing a network bottleneck which needs to be addressed.
]]>