Connection Pooling in SQL Server

Connection Pooling - SQL Server

Connection pooling can be really beneficial, as opening and maintaining a database connection for 

each user is costly and wastes resources. A connection pool is a set of reusable database 

connections maintained by the database server so that the connections can be reused.

However if the application code does not follow some basic principles then it can cause headaches 

for the SQL Server DBA.

Connection pooling problems are almost always caused by a connection leak. This is a condition 

where the application does not consistently close its database connections correctly. However, this 

could also be due to worker threads not being available to process the incoming login or the server

might be experiencing some CPU pressures. 

One of the biggest considerations within connection pooling is to ensure that the proper disposal or 

closure of the SqlConnection object(s) is taking place. When clients don’t close or dispose 

of the connection object properly  it will continue to consume the pooled connection, instead 

of releasing it back to the pool for reuse.This is a common pitfall for web applications.

Also, if the connection string is not an exact match to an existing pool when a new connection 

is opened, a new pool is created. Connection pooling is created for each distinct connection 

string, as such there will be as many connection pools as the number of connection 

strings used. If integrated security is used (connection string + the user identity) then you will 

get one pool per user. The user cannot take advantage of the connections made by 

other users. Also, if the application extracts data from many databases then there will be a 

separate pool of connections to each database.

As such you should ensure that developers are following the below simple steps within their code:

1. Only open a connection when you need it, not before.
2. Close your connection as soon as you are done using it.
3. Don't leave a connection open if it is not being used.
4. Be sure to drop any temporary objects before closing a connection.
5. Be sure to close any user-defined transactions before closing a connection.
6. Don't use application roles if you want to take advantage of connection pooling.

Other steps you can take to reduce potential problems are:

1. Reduce the number of integrated security accounts so accounts can share a pool.
2. Connect to the same database on the server and then switch the context 

(by executing the T-SQL USE statement) to the desired database. 

Again this will ensure that users accessing different databases can share a pool.

Some import connection string parameters that developers (and DBAs) need to be aware of are:

1. Connect Timeout - This controls the wait period in seconds when a new connection 

is requested, if this timeout expires, an exception will be thrown. The default is 15 seconds.

2. Max Pool Size - This specifies the maximum size of your connection pool. The default is 100.

The below query can be useful when investigating connection pool issues.

it shows the number of concurrent connection by user, for those users who have more

than 2 sessions connected to the DB. 

SELECT 
des.program_name,
des.login_name, 
des.host_name, 
COUNT(des.session_id) [Connections] 
FROM sys.dm_exec_sessions des
INNER JOIN sys.dm_exec_connections DEC 
ON des.session_id = DEC.session_id 
WHERE des.is_user_process = 1 
AND des.status != 'running' 
GROUP BY des.program_name, des.login_name, des.host_name 
HAVING COUNT(des.session_id) > 2 
ORDER BY COUNT(des.session_id) DESC;

If you need to clear your connection pool programmatically then you can do this using .NET 2.0.

ADO.NET 2.0 provides two static methods for doing this.

    SqlConnection.ClearPool( SqlConnectionObject ).
    SqlConnection.ClearAllPools().

The ClearAllPools method empties the connection pool.

If there are connections in use at the time of the call, they are marked appropriately 

and will be discarded when Close method is called on them. 

There is also a small application called connection pool cleaner created by uma075 

which has been specifically built for this purpose.
It can be downloaded from sourceforge.

This application could help if application users start to see timeout expired messages due 

to the max number of pooled connections being reached.

Timeout expired.  
The timeout period elapsed prior to obtaining a connection from the pool.  
This may have occurred because all pooled connections were in use and max pool size was reached.

Here are some useful related links:

SQL Server Central - connection pool limit exceeds error 

Idera - SQL Server connection pooling 

SQL Server Performance - connection pooling myths

Pythian - SQL Server understanding and controlling connection