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
