Summary
When you use the SQL Server ODBC driver, the SQL Server OLE DB provider, or the System.Data.SqlClient managed provider, you can disable connection pooling by using the respective application programming interfaces (APIs). When you disable pooling, the stress on the underlying SQL Server network library may be increased if your application frequently opens and closes connections. This article describes certain TCP/IP settings that you may have to adjust under these conditions.
More Information
Turning off pooling can cause the underlying SQL Server network driver to rapidly open and close new socket connections to the computer that is running SQL Server. You may have to change default TCP/IP socket settings for the operating system and the computer that is running SQL Server to deal with the higher stress levels.
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:
SQL Server Does Not Exist Or Access Denied
Timeout Expired
General Network Error
TCP Provider: Only one usage of each socket address (protocol/network address/port) is normally permitted.
Note that you can also receive these specific error messages when other problems are occurring with SQL Server; for example, you may receive these error messages if the remote computer that is running SQL Server is shut down, if the remote computer that is running SQL Server is not listening to TCP/IP sockets at all, if network connectivity to the computer that is running SQL Server is broken because the network cable is pulled out, or if you are having DNS resolution issues. Basically anything that can cause the client to fail to open a TCP/IP socket to the computer that is running SQL Server can also cause the error messages. However, with a stress-related socket issue, the problem occurs intermittently as the stress rises and falls. The computer may run for hours with no errors, then the error occurs one or two times, and the computer then runs for several more hours with no errors. Also, when you have this problem, general connectivity to SQL Server works one instant, fails the next, then works again the next instant. In other words, stress-related socket issues typically occur sporadically, but real network connectivity problems with SQL Server typically do not occur sporadically.
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:
319502 PRB: 'WSAEADDRESSINUSE' Error Message When You Try to Connect Through an Anonymous Port After You Increase the IMAP Connection Limit
Adjust the MaxUserPort and TcpTimedWaitDelay settings
Note 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 -n
Active 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
If you run netstat -n and you see that close to 4000 connections to the IP address of the target computer that is running SQL Server are in a TIME_WAIT state, you can both increase the default MaxUserPort setting and reduce the TcpTimedWaitDelay setting so that you do not run out of client anonymous ports. For example, you can set the MaxUserPort setting to 20000 and set the TcpTimedWaitDelay setting to 30. A lower TcpTimedWaitDelay setting means that the sockets wait in the TIME_WAIT state for less time. A higher MaxUserPort setting means that you can have more sockets in the TIME_WAIT state.
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:
812873 How to reserve a range of ephemeral ports on a computer that is running Windows Server 2003 or Windows 2000 Server
Adjust the WinsockListenBacklog setting
For additional information about this SQL Server-specific registry setting, click the article number below to view the article in the Microsoft Knowledge Base:
154628 INF: SQL Logs 17832 with Multiple TCP\IP Connection Requests
When the SQL Server network library listens on TCP/IP sockets, the SQL Server network library uses the listen Winsock API. The second parameter for the listen API is the backlog that is allowed for the socket. This backlog represents the maximum length of the queue of pending connections for the listener. When the length of the queue exceeds this maximum length, the SQL Server network library immediately rejects more TCP/IP socket connection attempts. Additionally, the SQL Server network library sends an ACK+RESET packet.
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 properties
ETHERNET: EType = Internet IP (IPv4)
IP: Protocol = TCP - Transmission Control; Packet ID = 40530; Total IP Length = 40; Options = No Options
TCP: 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 the source port is 0x599, or 1433 in decimal. This means that the packet comes from a typical computer that is running SQL Server and that is running on the default port of 1433. Also note that the Acknowledgement field significant and the Reset the connection flags are set. If you are familiar with filtering a Network Monitor trace, you can filter the TCP Flags value by 0x14 hexadecimal to see only the ACK+RESET packets in the Network Monitor trace.
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.
