Network Tuning

Network tuning with respect to SQL Server performance is affected by the following:

Throughput of the LAN or WAN.
Throughput of the server network interface card.
Availability of resources on the server to service client requests.

The throughput of the LAN or WAN is not critical to the tuning of a specific SQL Server. However, when considering remote procedure calls between SQL Servers or data replication, LAN and/or WAN throughput will be an important concern. Thus, this section will deal with tuning issues related to the network interface card and system or SQL Server resources that affect the SQL Server's network performance. Accordingly, the following network bottleneck-monitoring guidelines will deal with these issues.

Symptoms and Actions

Symptom: On a dedicated SQL Server system, if SQLServer: NET - Network Reads/sec is substantially lower than Server: Bytes Received/sec, this may indicate network activity beyond that generated by SQL Server. This may also be caused by periodic Windows NT Server administrative processes if the system also serves as a primary domain controller (PDC) or backup domain controller (BDC).
Action: Determine if any processes or protocols extraneous to the operation of SQL Server are running. If so, stop them.
Symptom: On a dedicated SQL Server system, if SQLServer: NET - Network Writes/sec is substantially lower than Server: Bytes Transmitted/sec, this may indicate network activity beyond that generated by SQL Server. This may also be caused by periodic Windows NT Server administrative processes if the system also serves as a PDC or BDC.
Action: Determine if any processes or protocols extraneous to the operation of SQL Server are running. If so, stop them.
Symptom: If SQLServer: NET - Network Reads/sec or SQLServer: NET - Network Writes/sec is high this indicates a great deal of network traffic.
Action: Look at the number of SQLServer: User Connections and the SQLServer: Net - Command Queue Length. If these values are also high, especially Net - Command Queue Length, then consider increasing the memory allocated to SQL Server. However, you may want to restrict user connections with sp_configure to decrease the workload on the SQL Server. Remember, user connections and worker threads are counted as overhead against the SQL Server memory allocation. Thus, plan accordingly when adjusting these values.

General Actions for Network Tuning

Tabular data stream (TDS) is the application protocol used by SQL Server for the transfer of requests and request results between clients and servers. TDS data is sent in fixed-size chunks, called packets. TDS packets have a default size of 512 bytes. If an application does bulk copy operations or sends or receives large amounts of text or image data, a packet size larger than 512 bytes may improve network performance, since it results in fewer reads and writes. For large data transfers, a packet size between 4,092 and 8,192 is usually best. Any larger size packet may degrade performance.
To set the server's default packet size, set network packet size in sp_configure. Packet size can be configured in ISQL/w for the current ISQL/w connection only. Configuring the packet size gives you a choice between responsiveness and overall throughput. Larger packets provide less packet overhead and provide better end-to-end throughput: An entire task completes more quickly with larger packets. Smaller packets are more responsive for those interested in intermediate results. Overall, a smaller packet size provides quicker response time, but usually, an entire task takes longer to complete with smaller packets than with larger packets. For more information about configuring packet size, see SQL Server Books Online.

You can monitor the improvement in network read and write efficiency by viewing the SQLServer: NET - Network Reads/sec and SQLServer: NET - Network Writes/sec counters before and after changing the TDS packet size. Fewer reads and writes should occur after increasing the packet size.
Make sure the server throughput is set to Maximize Throughput for Network Applications; this is the default for SQL Server. However, with this option set, user application access has priority over file cache access to memory (4 MB of memory is allocated to available memory for starting up and running local applications).
Increase update-notice periods for Netlogon service on the SQL Server if your SQL Server is a PDC. Also increase the server announcement period if you are concerned with the amount of maintenance traffic the Windows NT Server is creating and the load on the PDC.

Value name	Default value	Minimum value	Maximum value

PulseConcurrency	20	1	500
Pulse	300 (5 minutes)	60 (1 minute)	3600 (1 hour)
Randomize	1 (1 second)	0 (0 seconds)	120 (2 minutes)

PulseConcurrency defines the maximum number of simultaneous pulses the PDC sends to BDCs.
Netlogon sends pulses to individual BDCs. The BDCs respond by asking for any database changes. To control the maximum load these responses place on the PDC, the PDC will only have PulseConcurrency pulses "pending" at one time. The PDC should be sufficiently powerful to support this many concurrent replication RPC calls (related directly to Server service tuning as well as the amount of memory in the computer).

Increasing PulseConcurrency increases the load on the PDC. Decreasing PulseConcurrency increases the time it takes for a domain with a large number of BDCs to get a SAM/LSA change to all of the BDCs. Consider that the time to replicate a SAM/LSA change to all the BDCs in a domain will be greater than: ((Randomize/2) * NumberOfBDCsInDomain) / PulseConcurrency
Pulse defines the typical pulse frequency (in seconds). All SAM/LSA (User/Security account database) changes made within this time are collected together. After this time, a pulse is sent to each BDC needing the changes. No pulse is sent to a BDC that is up-to-date.
If you need to transfer huge amounts of data between different computer systems, Ethernet may not be the appropriate medium to use; the basic Ethernet cable is limited to 10 MB/sec (considerably less when you include network overhead). Other media are now available that offer significantly higher sustained transfer rates (FDDI, ATM, and so on).
The Network Monitor (provided with Microsoft Systems Management Server) is a good tool to use to monitor general network performance. It offers additional Performance Monitor counters as well as a few unique statistics from within the application.
Match the network interface card to the system bus. If you have a 16-bit bus, use a 16-bit network adapter; if you have a 32-bit bus, use a 32-bit network adapter. In addition, the card should be a bus master card to minimize the processing overhead associated with network interrupts.