Connection pooling problems are almost always caused by a "connection leak" - 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 their Dispose method. Unlike old ADO, ADO.NET requires you to manually close your database connections as soon as you're done with them. If you think of relying on connection objects to go out of scope, think again. It may take hours until GC collects them. In the mean time your app may be dead in the water, greeting your users or support personnel with something like this: 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()
...
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()
Closing your connections
When you intend to close your database connection, you want to make sure that you are really closing it. The following code looks fine yet causes a connection leak:
SqlConnection
conn = new SqlConnection(myConnectionString);
conn.Open();
doSomething();
conn.Close();
If doSomething() throws an exception - conn will never get explicitly closed. Here is how this can be corrected:
SqlConnection
conn = new SqlConnection(myConnectionString);
try
{
conn.Open();
doSomething(conn);
}
finally
{
conn.Close();
}
or
using (SqlConnection conn = new SqlConnection(myConnectionString))
{
conn.Open();
doSomething(conn);
}
Did you notice that in the first example we called conn.Close() explicitly while in the second one we make the compiler generate an (implicit) call to conn.Dispose() immediately following the using block? The C# using block guarantees that the Dispose method is called on the subject of the using clause immediately after the block ends. Close and Dispose methods of Connection object are equivalent. Neither one gives you any specific advantages over the other.
When returning a connection from a class method - make sure you cache it locally and call its Close method. The following code will leak a connection:
OleDbCommand
cmd new OleDbCommand(myUpdateQuery, getConnection());
intres = cmd.ExecuteNonQuery();
getConnection().Close(); // The connection
returned from the first call to getConnection() is not being closed. Instead of
closing your connection, this line creates a new one and tries to close it.
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.
Last but not the least, never Close or Dispose your connection or any other managed object in the class destructor or your Finalize method. This not only has no value in closing your connections but also interferes with the garbage collector and may cause errors. For more information see http://msdn.microsoft.com/library/en-us/cpguide/html/cpconprogrammingessentialsforgarbagecollection.asp.
Testing your changes
The only way to know the effect of your changes on connection pooling behavior is to load-test your application. If you have existing unit tests - use them. Running your unit tests repeatedly in a loop may create a fair bit of stress on application. If you don't, use the Web load testing tool. There are plenty of commercial load testing tools on the market. If you prefer freeware, consider OpenSTA available at www.opensta.org. All you need to setup your load test is to install the tool, bring up your Web application and click your way through. OpenSTA will record your HTTP requests into test scenarios that you can run as part of your load test.
Knowing that your application crashes under the load doesn't often help to locate the problem. If the app crashes fairly quickly, all you may need to do is run several load tests - one for each module and see which one has a problem. However, if it takes hours to crash you will have to take a closer look.
Monitoring connection pooling behavior
Most of the time you just need to know if your application manages to stay within the size of its connection pool. If the load doesn't change, but the number of connections constantly creep even after the initial "warm-up" period, you are most likely dealing with a connection leak. The easiest way to monitor the number of database connections is by using the Performance Monitor available under Administrative tools on most Windows installations. If you are running SQL Server, add SQL Server General Statistics -> User Connections performance counter (The counter is available on the SQL Server machine so you may need to put its name or IP address into the Select Counters From Computer box). The other way to monitor the number of database connections is by querying your DBMS. For example, on SQL Server run:
EXEC SP_WHO
Or on Oracle, run:
SELECT * FROM V$SESSION WHERE PROGRAM IS NOT NULL
.NET CLR Data performance counters
In documentation you may run into .Net CLR Data performance counters. They are great if you know what they can and cannot do. Keep in mind that they do not always reset properly. The following KB article sheds some light on the problem but in my opinion does not cover all the issues: http://support.microsoft.com/default.aspx?scid=kb;en-us;314429. Another thing to keep in mind is that IIS unloads app domains under stress so don't be surprised when your number of database connections has dropped to zero while your min pool size is five!
Short term fixes
What if you discovered the connection pooling issue in production and you cannot take it offline to troubleshoot? Turn pooling off. Even though your app will take a performance hit, it shouldn't crash! Your memory footprint will also grow. What if it doesn't crash all that often, and you don't want to take a performance hit? Try this:
conn = new SqlConnection();
try
{
conn.ConnectionString = "integrated
security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool
Size=60;Connect Timeout=2;"; // Notice Connection Timeout set to only two seconds!
conn.Open();
}
catch(Exception)
{
if (conn.State != ConnectionState.Closed) conn.Close();
conn.ConnectionString = "integrated
security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Pooling=false;Connect
Timeout=45;";
conn.Open();
If I fail to open a pooled connection within two seconds, I am trying to open a non-pooled connection. This introduces a two second delay when no pooled connections are available, but if your connection leak doesn't show most of the time, this is a good steam valve.
