To diagnose network problems, our team pays special attention to the NETWORKIO wait type, which the "Wait Types, Perfmon Counters, and Correlations" document includes. (You can download the full document at InstantDoc ID 40925.) The NETWORKIO wait type indicates that SQL Server has sent results to the client and is waiting for an acknowledgement. Network bandwidth and packet sizes are important considerations when NETWORKIO is high. You can think of packets as the containers that an application sends queries to SQL Server in; SQL Server uses these same containers to return results to the client. Packets/sec is the rate at which the network data travels across the network. You can obtain the average number of bytes a packet transfers by dividing the Bytes Total/sec Performance Monitor counter by Packets/sec.
If the NETWORKIO percentage is high, you should look at SQL Server's packet size. SQL Server's default packet size is 4096 bytes. If an application performs bulk-copy operations or sends or receives large amounts of text or image data, a packet size larger than the default can improve efficiency. A larger packet size lets you process fewer packets overall, so you need fewer network reads and writes. If an application sends and receives small amounts of information, you can set the packet size to 512 bytes, which is sufficient for most data transfers. The most important tips for alleviating NETWORKIO wait problems are to make sure the packet size lets SQL Server send as much data as possible in each packet and to avoid sending empty packets and wasting network bandwidth. Before making changes, confer with your network administrator to determine the optimal packet size for your organization.
