C# SqlConnections using up entire connection pool

Question

I've written a service that occasionally has to poll a database very often. Usually I'd create a new SqlConnection with the SqlDataAdapter and fire away like this:

var table = new DataTable();

using(var connection = new SqlConnection(connectionString))
{
    connection.Open();
    using(var adapter = new SqlDataAdapter(selectStatement, connection))
    {
        adapter.Fill(table);
    }
}

However in a heavy load situation (which occurs maybe once a week), the service might actually use up the entire connection pool and the service records the following exception.

System.InvalidOperationException: 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.

Multiple threads in the service have to access the SQL server for various queries and I'd like as much of them to run in parallel as possible (and that obviously works too well sometimes).

I thought about several possible solutions:

• I thought about increasing the connection pool size, however that might just delay the problem.
• Then I thought about using a single connection for the service and keep that open for the remainder of the service running, which might a simple option, however it will keep the connection open even if there is no workload to be done and would have to handle connection resets by the server etc. which I do not know the effect of.
• Lastly I thought about implementing my own kind of pool that manages the number of concurrent connections and keeps the threads on hold until there is a free slot.

What would be the recommended procedure or is there a best practice way of handling this?

Answer 1

Well the solution in the end was not exactly ideal (fixing the issue on the SQL Server side) so I ended up checking the number of concurrent connections in the job queuing system.

The service will now not create another thread for document generation unless it can guarantee that the connection pool is actually available. The bad bottleneck on the SQL server is still in place, however the service now no longer generates exceptions.

The downside of course is, that the queue gets longer while there is some blocking query executing on the SQL Server, which might delay document generation for a minute or two. So it isn't an ideal solution but a workable one, since the delay isn't critical as the documents aren't needed directly but stored for archival purpose.

The better solution would have been to fix it SQL Server side.