Yesterday we had a very strange problem in production with an old sproc-based ASP.NET web application. We had just switched from using a handful of “SqlHelper” classes to the Microsoft Enterprise Library Data Access Application block (DAAB).
Strangely though, after releasing these changes to production, the application became plagued by connection and memory leaks, causing connection pool overflows (over 200 connections to the SQL box at one point) and the IIS application pool to fall over every few minutes.
Memory leaks are notoriously difficult to diagnose. We eventually were able to narrow it down to this offending piece of code, used for running FOR XML queries:
public XmlDocument ExecuteNativeXml(string sprocName, params object[] parameters){ SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase(); using (DbCommand command = db.GetStoredProcCommand(sprocName, parameters)) { XmlDocument document = new XmlDocument(); using (XmlReader reader = db.ExecuteXmlReader(command)) document.Load(reader); return document; }} Can you spot the memory leak? Everything that implements IDisposable is wrapped in a using block, and DAAB takes care of opening/closing SqlConnections — our code doesn’t have any contact with them at all. So what’s the problem then?
This article about connection pools in SQLMag from 2003 explains it:
…My test application shows that even when you use this [CommandBehavior.CloseConnection] option, if you don’t explicitly close the DataReader (or SqlConnection), the pool overflows. The application then throws an exception when the code requests more connections than the pool will hold.
Some developers insist that if you set the CommandBehavior.CloseConnection option, the DataReader and its associated connection close automatically when the DataReader finishes reading the data. Those developers are partially rightbut the option works this way only when you’re using a complex bound control in an ASP.NET Web application. Looping through a DataReader result set to the end of its rowset (that is, when the DataReader’s Read method returns false) isn’t enough to trigger automatic connection closing. However, if you bind to a complex bound control such as the DataGrid, the control closes the DataReader and the connection but only if you’ve set the CommandBehavior.CloseConnection option.
If you execute a query by using another Execute method (e.g., ExecuteScalar, ExecuteNonQuery, ExecuteXMLReader), you are responsible for opening the SqlConnection object and, more importantly, closing it when the query finishes. If you miss a close, orphaned connections quickly accumulate.
The fix is pretty bit ugly and unexpected — you have to reach inside the DBCommand and explicitly close its connection yourself:
public XmlDocument ExecuteNativeXml(string sprocName, params object[] parameters){ SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase(); using (DbCommand command = db.GetStoredProcCommand(sprocName, parameters)) { XmlDocument document = new XmlDocument(); using (XmlReader reader = db.ExecuteXmlReader(command)) document.Load(reader); // If you do not explicitly close the connection here, it will leak! if (command.Connection.State == ConnectionState.Open) command.Connection.Close(); return document; }} So the bug was not the fault of DAAB, but it was caused by its use of CommandBehavior.CloseConnection — the recommended technique. A nice trap for young players!
