Sql Connection Pooling in .Net

Sql Connection pooling is one of the hidden heros that not many developers I know will take time to look into.

Granted, ADO.NET covers up so much of whats really going on, and takes care of of the nitty gritty details, so as a .net developer, you should have to worry too much -> WRONG! When it comes to optimizing performance, you NEED to know whats really happening!

Sql Connection Pool Basics: 

Fact: Opening a database connection is a resource intensive and is a time consuming operation.

A connection pool is a way to cache sql connections that are maintained so that the connections can be reused when future requests to connect to a database is needed. By reusing active database connections,  the performance of .Net applications, be it Web or Windows,  is increased!

Connection Pools in .Net are managed and maintained by the Connection Pool Manager.  When a new connection request come in, the Connection Pool Manager will check if the current connection pool contains any unused connections. If there are connections available it will return it, otherwise if all connections currently in the pool are busy and the maximum pool size has not been reached, a new connection is created and added to the pool. When the pool reaches its maximum size, all new requests are queued until a connection in the pool becomes available or the connection attempt times out.

Controlling the connection pooling behavior can be done via the use of connection string parameters. Below are the most common parameters that control most of the connection pooling behavior:

• Connect Timeout - controls the wait period in seconds when a new connection is requested (As mentioned above), if this timeout expires, an exception will be thrown. The default connection timout is 15 seconds.
• Max Pool Size - specifies the maximum size of your connection pool. By default it is 100 connections. 
• Min Pool Size - specifies the initial number of connections that will be added to the pool upon its creation. By default, this value is 0.
• Pooling - controls whether or not to use connection pooling. The default value is true.

Most Common Issues and Resolutions

The following exception is most probably the most common issue:
"Exception: System.InvalidOperationException
Message: 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.
Source: System.Data  

at  System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
   at System.Data.SqlClient.SqlConnection.Open()"

This exception usually happens due to Connections Leaks - a condition where your application does not close its database connections correctly and consistently.

When you "leak" connections, they remain open until the garbage collector (GC) closes them for you by calling the Dispose method on your SqlConnection object. Unlike the old ADO, ADO.NET requires you to manually close your database connections as soon as you're done with them. As we cannont rely on the GC to do the work for us, as GC can take a very long time to collect, we have to do this in code. There are 3 ways to achieve this:

Using the Close() method:

SqlConnection conn = new SqlConnection(myConnectionString);

conn.Open();

//execute query

conn.Close();

Using a "Try...Finally" clause:

SqlConnection conn = new SqlConnection(myConnectionString);

try

{

   conn.Open();

   //execute query

}

finally

{

   conn.Close();

}

and using the "using()" statement:

using (SqlConnection conn = new SqlConnection(myConnectionString))

{

conn.Open();

//execute query

}

If you use SqlDataReader, OleDbDataReader, etc., close them. Even though closing the connection itself seems to do the trick, put in the extra effort to close your data reader objects explicitly when you use them!

For more information, you can read:

http://betav.com/blog/billva/2007/05/managing_and_monitoring_net_co.html