.Net Multithreading: SQL ConnectionPool

Question

In a VB.Net Windows Service I'm currently pooling units of work with:

ThreadPool.QueueUserWorkItem(operation, nextQueueID)

In each unit of work (or thread I'll use for ease of understanding), it will make a couple MSSQL operations like so:

    Using sqlcmd As New SqlCommand("", New SqlConnection(ConnString))
        With sqlcmd
            .CommandType = CommandType.Text
            .CommandText = "UPDATE [some table]"

            .Parameters.Add("@ID", SqlDbType.Int).Value = msgID

            .Connection.Open()
            .ExecuteNonQuery()
            .Connection.Close()   'Found connections not closed quick enough'
        End With
    End Using

When running a netstat -a -o on the server I'm seeing about 50 connections to SQL server sitting on IDLE or ESTABLISHED, this seems excessive to me especially since we have much larger Web Applications that get by with 5-10 connections.

The connection string is global to the application (doesn't change), and has Pooling=true defined as well.

Now will each of these threads have their own ConnectionPool, or is there one ConnectionPool for the entire .EXE process?

Answer 1

From the MS Docs -

"Connections are pooled per process, per application domain, per connection string and when integrated security is used, per Windows identity"

http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

Are you experiencing errors such as -

Exception Details: 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.

Also how many work items are being queued in the service?

Answer 2

If the number of open connections offends you, take control in the connection string

Notice: MinPoolSize and MaxPoolSize.

Answer 3

One big problem with your code is that you aren't closing your connection if ExecuteNonQuery throws an exception. Disposing the SqlCommand is not enough, you need to also dispose the SqlConnection when an exception is thrown, something like:

Using SqlConnection connection = New SqlConnection(ConnString)
    Using sqlcmd As New SqlCommand("", connection)        
     With sqlcmd            
       ... etc
     End With    
    End Using
End Using

Answer 4

Although I generally like the using statement, I find that sometimes in the .NET libraries the actual CLOSE of a handle isnt done until garbage collection. So being old school and religious about such things, I add an explicit close at the end of my using statement. This is pseduo code since I generally code in C# rather then VB.NET but it should give you the idea.

Using SqlConnection connection = New SqlConnection(ConnString)

   TRY
      Using sqlcmd As New SqlCommand("", connection)
                  With sqlcmd
                        ... etc
          End With
      End Using
   FINALLY
      connection.Close()

End Using