ADO.net is not closing TCP connections fast enough

Question

I'm running a multi-threaded operation that fetches data from a SQL Server database using SqlConnection and Parallel.ForEach() and following is happening:

• I wrap SqlConnection in a using statement so that the connection is properly disposed.
• My process consistently throws an SqlException wrapped in an AggregateException after running successfully for some time ("A network-related or instance-specific error occurred while establishing a connection to SQL Server..")
• I found that this happens right around 2^14 (16384) calls to the database (in total, across all threads).
• I fired up perfmon and I can see that that's also the number of TCP connections that are open right around the time that the exception is thrown ("Connections Established" counter).
• I'm certain that there's no connection leakage in my code - there are very few places where I am querying the database and they all properly dispose the connection (in fact, there is no other pattern I use for querying the database other than vanilla using(...))
• I turned off the connection pool and the same behavior occurs.
• Oddly, if I delete an index in SQL Server that makes my queries fast, the operation completes successfully (albeit very slowly) - no exception is thrown. I observe that # of Connections Established rises linearly to about 13K and then stabilizes for some time, and then there's some periods with a linear decline, all while the operation is running.
• My conclusion is that with the index established, .net is processing data more quickly than it can close out the connections and eventually hits some kind of OS or .NET socket max threshold. Without the index, .NET is still maintaining too many connections, but it has the time to close enough of them down so that the max open sockets threshold is not hit.

I'm at a loss on how to instruct .NET to close-out those connections. I thought that that happened auto-magically when the SqlConnection is disposed.

Answer 1

The issue seems to be with how the connection is opened. I'm using this constructor

public SqlConnection(string connectionString, SqlCredential credential)

It seems that ADO creates a new connection pool, even when connectionString is the same and credential encapsulates the same credentials. My guess is that ADO somehow can't relate the credential to prior calls (maybe it would work if I was using the same credential object by using reference equality? In my case, I create a new SqlCredential with each call).

Because of a new pool is created each time, the number of TCP connections balloons. I think this also makes sense when the connection pool is turned off. Likely some of the socket or TCP settings are kicking-in here (keep alives?) and the OS is keeping the connections open to respect those settings.