Improve SQL & NIC Performance

Have you ever found your SQL server drops packets or experiences network latency when network traffic is at a minimum? Or perhaps searching through log files only to find the following error logs listed below.

All schedulers on Node 0 appear deadlocked due to a large number of worker threads waiting on ASYNC_NETWORK_IO. Process Utilization 0%.

This particular error is due to network buffer space errors. The network adapter is transmitting data faster than it is able to receive data. The SQL application sends network packets to the loopback address,  but the local NIC receiver buffer space is insufficient.

This can be explained in more details in the following articles listed below.

http://www.confio.com/English/Tips/Async_Network_IO.php

You are probably wondering what are a Receive Descriptors and Transmit Descriptors, they are parameters found on certain NIC adapters which can be manipulated if needed to accommodate network traffic.

Receive Descriptors

Sets the number of buffers used by the driver when copying data to the protocol memory. Increasing this value can enhance receive performance, but also consumes system memory.

Receive Descriptors are data segments that enable the adapter to allocate received packets to memory. Each received packet requires one Receive Descriptor, and each descriptor uses 2KB of memory.

Transmit Descriptors

Defines the number of Transmit Descriptors, which are data segments that enable the adapter to track transmit packets in the system memory.  Depending on the size of the packet, each transmit packet requires one or more Transmit Descriptors.

You might choose to increase the number of Transmit Descriptors if you notice a problem with transmit performance. Increasing the number of Transmit Descriptors can enhance transmit performance. However,  Transmit Descriptors consume system memory. If transmit performance is not an issue, use the default setting.

Where are these Descriptors located on a network adapter? They are depicted in the screen shots below. (click images to enlarge)

The great differences in both buffer sizes can contribute to network bottleneck on the NIC adapter. Most adapters seem to have configured in a specific manner but might need to change at some point to avoid network buffer space errors.

To correct this buffer space error, increase the Receive Buffer from 256 to either 512 or 504, this will use up some Physical Memory on the server but should increase network performance on the NIC adapter.