Perhaps Garbage Collection is spoiling us as developers. The Garbage Collector is like our own maid following us around picking up every mess we create. Anway, .NET does a great job managing many resources for us including things like memory and conncetion pooling. However, this doesn't mean we are completely off the hook.
As Dave Strommer recently posted http://drowningintechnicaldebt.com/blogs/davidstrommer/archive/2006/11/10/Connection-Pool-timeout-expired.aspx we experiecened the result of bad assumptions and just lazy coding.
We learned that .NET 2.0 introduced a new counter called NumberOfReclaimedConnections. This counter is very important b/c you can confirm an application is leaking connections by watching this counter increase.
Here are some tips to avoid drowning in the connection pool:
- Close Connections as quick as possible. Get In and Get Out.
- Do not increase the Max Pool Size Parameter. The default setting of 100 can support 1000's and 1000's of connections. If you're experiencing problems something is likely wrong and increasing the Max Pool Size is just a Band Aid that will increase your Technical Debt!
- Enclose connections with using statements.
using (SqlConnection conn = new SqlConnection(myConnectionString))
{
conn.open
// do whatever
} // connection is wrapped in using so it is closed.
- Enclose data readers with using statements or include try, catch, finally