How to reduce the amount of memory used up by the Sql connection pool in a C# program

Question

I have a C# program which acts as a multi-threaded web server. It does lots of processing of Xml structures held in a Sql Server database.

As the size of these Xml structures increases, I am finding the app running out of memory.

I have deployed the ANTS memory profiler to see what is happening, and have managed to reduce the number of large strings held in memory during processing, and improved things a bit.

However, I am now left with a fragmented large object heap, caused by large byte arrays held in the connection pool. The large byte arrays are

TdsParserStateObject._bTmp
in TdsParser._physicalStateObj
in SqlInternalConnectionIds._parser
in DbConnectionInternal[0]
in DbConnectionPool._objectList

I am 99.9% sure I am only using connections within using statements, although I do keep one connection per thread open while the thread is running (which is intended as an optimisation, but I'm suspicious whether it is making things worse).

Is there anything I can do to a connection to reduce the amount of memory it holds (other than Closing or Disposing of it)?

Or should I just always Close or Dispose every connection immediately on every use?

[Later - as per my comment] I refactored the code to use a new connection for every database access, and dispose of it afterwards (except for transactions, of course, where I use the same connection from start to end of the transaction, and dispose it with the transaction).

Even when the program is idle (i.e. has no connections in use), there are still connections in the connection pool taking up lareg amounts of memory, and causing fragmentation.

Why would a disposed connection hold 58MB of memory in the connection pool?

[Even Later] I have a solution which prevents the Sql Server connection pool from fragmenting the large heap - this is to detect which connections will probably have a huge buffer, and mark them for removal from the pool on disposal, using

SqlConnection.ClearPool(connection)

I currently do this in a rather hacky way, by subclassing DataReader and detecting if any of the fields returned are over 10MB in size.

Suggestions as to a better way of detecting which Connections have large buffers are welcome.

Note that I have reverted out the changes to open and dispose of a connection on every database access, as keeping track of which connection to use while inside a transaction (has to be the transaction one, obviously) was doing my head in.

The connection opened at the start of the thread and closed at the end is fine, as nearly all the threads are short lived (being a response to a single web request). The exceptions are batch processes that probably operate in the context of a single transaction anyway.

Answer 1

There is no need to keep a connection open when you're using ADO.NET. Connections will be pooled for you by default (unless you turned off pooling yourself) and ADO.NET will do all the management of the pool. You don't even have to worry about different user credentials, etc. as connections are pooled only if the connection parameters are identical. From the memory usage perspective disposing of connection can only be helpful.

More about this:

• SQL Server Connection Pooling (ADO.NET) on MSND
• Best practice? open and close multi connections, or one large open connection for ado.net
• is it safe to keep database connections open for long time