Description of TCP/IP settings that you may have to adjust when SQL Server connection pooling is disabled
Note that this article only discusses settings that affect the SQL Server network library when you use the TCP/IP protocol. Turning off pooling can also cause stress-related problems with other SQL Server protocols such as named pipes, but this article does not discuss this topic. This article is for advanced users only. If you do not understand the topics in this article, Microsoft recommends that you see a good book about TCP/IP sockets.
Note that Microsoft strongly recommends that you always use pooling with the SQL Server drivers. Using pooling greatly improves overall performance on both the client side and SQL Server side when you use the SQL Server drivers. Using pooling also considerably reduces network traffic to the computer that is running SQL Server. For example, a sample test that used 20,000 SQL Server connection opens and closes with pooling enabled used about 160 TCP/IP network packets, for a total of 23,520 bytes of network activity. With pooling disabled, the same sample test generated 225,129 TCP/IP network packets, for a total of 27,209,622 bytes of network activity.
Note that when you see these stress-related TCP/IP socket issues with the SQL Server network libraries, you may receive one or more of the following error messages when you try to connect to a computer that is running SQL Server:
Two main stress-related issues typically occur when you disable pooling while you use the SQL Server TCP/IP protocol: You may run out of anonymous ports on the client computer, or you may exceed the default WinsockListenBacklog setting on the computer that is running SQL Server.
For additional information about anonymous ports, click the article number below to view the article in the Microsoft Knowledge Base:
Adjust the MaxUserPort and TcpTimedWaitDelay settingsNote that the MaxUserPort and TcpTimedWaitDelay settings are applicable only for a client computer that is rapidly opening and closing connections to a remote computer that is running SQL Server and that is not using connection pooling. For example, these settings are applicable on an Internet Information Services (IIS) server that is servicing a large number of incoming HTTP requests and that is opening and closing connections to a remote computer that is running SQL Server and that is using the TCP/IP protocol with pooling disabled. If pooling is enabled, you do not have to adjust the MaxUserPort and TcpTimedWaitDelay settings.
When you use the TCP/IP protocol to open a connection to a computer that is running SQL Server, the underlying SQL Server network library opens a TCP/IP socket to the computer that is running SQL Server. When it opens this socket, the SQL Server network library does not enable the SO_REUSEADDR TCP/IP socket option. For more information about the SO_REUSEADDR socket setting, see the "Setsockopt" topic in the Microsoft Developer Network (MSDN).
Note that the SQL Server network library specifically does not enable the SO_REUSEADDR TCP/IP socket option for security reasons. When SO_REUSEADDR is enabled, a malicious user can hijack a client port to SQL Server and use the credentials that the client supplies to gain access to the computer that is running SQL Server. By default, because the SQL Server network library does not enable the SO_REUSEADDR socket option, every time you open and close a socket through the SQL Server network library on the client side, the socket enters a TIME_WAIT state for four minutes. If you are rapidly opening and closing SQL Server connections over TCP/IP with pooling disabled, you are rapidly opening and closing TCP/IP sockets. In other words, each SQL Server connection has one TCP/IP socket. If you rapidly open and close 4000 sockets in less than four minutes, you will reach the default maximum setting for client anonymous ports, and new socket connection attempts fail until the existing set of TIME_WAIT sockets times out.
On the client side, you may have to increase the MaxUserPort and TcpTimedWaitDelay settings that are discussed in Q319502 when you have pooling disabled. The settings for these values are determined by how many SQL Server connection opens and closes occur on the client side. You can examine how many client ports are in a TIME_WAIT state by using the Netstat tool on the client computer. Run the Netstat tool with the -n flag as follows, and count the number of client sockets to your SQL Server IP address that are in a TIME_WAIT state. In this example, the IP address of the remote computer that is running SQL Server is 10.10.10.20, the IP address of the client computer is 10.10.10.10, and three established connections and two connections are in a TIME_WAIT state:
C:\>netstat -nActive Connections Proto Local Address Foreign Address State TCP 10.10.10.10:2000 10.10.10.20:1433 ESTABLISHED TCP 10.10.10.10:2001 10.10.10.20:1433 ESTABLISHED TCP 10.10.10.10:2002 10.10.10.20:1433 ESTABLISHED TCP 10.10.10.10:2003 10.10.10.20:1433 TIME_WAIT TCP 10.10.10.10:2004 10.10.10.20:1433 TIME_WAIT
Note that if you adjust the MaxUserPort or TcpTimedWaitDelay setting, you must restart Microsoft Windows for the new setting to take effect. The MaxUserPort and TcpTimedWaitDelay settings are for any client computer that is talking to a computer that is running SQL Server over TCP/IP sockets. These settings do not have any effect if they are set on the computer that is running SQL Server unless you are making local TCP/IP socket connections to the local computer that is running SQL Server.
Note If you adjust the MaxUserPort setting, we recommend that you reserve port 1434 for use by the SQL Server Browser service (sqlbrowser.exe). For more information about how to do this, click the following article number to view the article in the Microsoft Knowledge Base:
Adjust the WinsockListenBacklog settingFor additional information about this SQL Server-specific registry setting, click the article number below to view the article in the Microsoft Knowledge Base:
SQL Server 2000 uses a default listen backlog setting of 5. This means that the computer that is running SQL Server passes the value 5 to the backlog parameter of the listen Winsock API when the listen API sets up the TCP/IP protocol listening threads on the computer that is running SQL Server. You can adjust the WinsockListenBacklog registry key to specify a different value to be passed for this parameter. Starting in SQL Server 2005, the network library passes a value of SOMAXCONN as the backlog setting to the listen API. SOMAXCONN allows the Winsock provider to set a maximum reasonable value for this setting. Therefore, the WinsockListenBacklog registry key is no longer used or needed in SQL Server 2005.
The backlog setting works as follows: Suppose an arbitrary service is listening for incoming TCP/IP socket requests. If you set the backlog setting to 5 and many socket connection requests are continually streaming in, the service may not be able to respond to the incoming requests as fast as they come in. At this point, the TCP/IP socket layer queues these incoming requests in the backlog queue, and the service can later pull the requests out of this queue and handle the incoming socket connection request. After the queue fills up, the TCP/IP socket layer immediately rejects any additional socket requests that come in by sending an ACK+RESET packet back to the client. Increasing the backlog queue size increases the number of pending socket connection requests that the TCP/IP socket layer queues before requests are rejected.
Note that the WinsockListenBacklog setting is specific to SQL Server. SQL Server tries to read this registry setting when the SQL Server service first starts. If the setting does not exist, the default of 5 is used. If the registry setting exists, SQL Server reads the setting and uses the supplied value as the backlog setting when the WinSock API listen is called as the TCP/IP socket listening threads are set up inside SQL Server.
To determine if you are running into this problem, you can run a Network Monitor trace on the client or the computer that is running SQL Server and look for socket connection requests that are immediately rejected with an ACK+RESET. If you examine TCP/IP packets in Network Monitor, you see a packet such as the following when this issue is occurring:
Frame: Base frame propertiesETHERNET: EType = Internet IP (IPv4) IP: Protocol = TCP - Transmission Control; Packet ID = 40530; Total IP Length = 40; Options = No OptionsTCP: Control Bits: .A.R.., len: 0, seq: 0-0, ack:3409265780, win: 0, src: 1433 dst: 4364 TCP: Source Port = 0x0599 TCP: Destination Port = 0x110C TCP: Sequence Number = 0 (0x0) TCP: Acknowledgement Number = 3409265780 (0xCB354474) TCP: Data Offset = 20 bytes TCP: Flags = 0x14 : .A.R.. TCP: ..0..... = No urgent data TCP: ...1.... = Acknowledgement field significant TCP: ....0... = No Push function TCP: .....1.. = Reset the connection TCP: ......0. = No Synchronize TCP: .......0 = Not the end of the data TCP: Window = 0 (0x0) TCP: Checksum = 0xF1E7 TCP: Urgent Pointer = 0 (0x0)
Note that you can also see similar ACK+RESET packets if the computer that is running SQL Server is not running at all or if the computer that is running SQL Server is not listening to TCP/IP protocol, so seeing ACK+RESET packets is not definite confirmation that you are having this problem. If the WinsockListenBacklog is too low, some connection attempts receive accept packets and some connections immediately receive ACK+RESET packets in the same timeframe.
Note that in very rare cases, you may have to adjust this setting even if pooling is enabled on the client computers. For example, if many client computers are talking to a single computer that is running SQL Server, a large number of simultaneous incoming connection attempts may occur at any particular time even if pooling is enabled.
Note If you adjust the WinsockListenBacklog setting, you do not have to restart Windows for this setting to take effect. Just stop and restart the SQL Server service for the setting to take effect. The WinsockListenBacklog registry setting is only for the computer that is running SQL Server. It does not have any effect on any client computer that is talking to SQL Server.
Article ID: 328476 - Last Review: 06/19/2014 15:07:00 - Revision: 12.0
- kbsqlsetup kbinfo KB328476