Best way to handle sql-connections in webservice?

Question

I have a webservice that is called by up to 10 clients. The webservice is built up of 7 differnet asmx-pages and have about 100-200 functions in each page.

All those functions are working against a MSSQL2005 or MS SQL2000 database. Some periods of the day its heavy traffic from the clients and it seems like I run out of connections on the sql-server causing all clients to stop.

In every function I open a connection, do the stuff and then close the connection, sometimes with transactions sometimes without.

On the server I see that it creates a lots of connections, I dont know why they dont goes away, but stays there even after the function is done and over. So I can se that my 10 clients creates over 80 connections from time to time. Sometimes some of them goes away, sometime they are still there hours after used. Is there some kind of pooling going on there?

Question 1: Are there another way to handle connections I should use, like one connection globally per webservice or any other way?

Question 2: If its ok to handle connections per function then why does it not close the connection on the server, making the list of open connections larger and larger all the time until I got out of connections-error?

This question is related to my other question but not the same: http://stackoverflow.com/questions/407320/strange-sql2005-problem-sqlconnection-does-not-support-parallel-transactions

I have now narrowing it down to the "out of connections" error.

Answer 1

So that's 700-1400 functions in a single web service? Sounds too big to me. Time to refactor.

I don't know what the connection between pages and web services is. I usually think of them as XML messaging endpoints, completely separate from the client that consumes them.

Answer 2

I agree on the comment about refactoring, but that's not the pertinent issue here.

You definitely should use a connection in each function, and it sounds like you aren't disposing of them correctly. Assuming the database operations are contained to the function you are calling, your code should look something like this:

using (SqlConnection connection = <connection code>)
{
  using (SqlCommand command = <command code>)
  {
    // Execute.
  }
}

On the server side, the connections are going to stay open. By default, the SqlConnection class enables connection pooling, so you are going to see the connection open on the server end.

This behavior is normal and should be expected.

Answer 3

You are actually seeing connection pooling in operation. This happens by default in .Net 2+ to SQL 2005 (not sure about other versions).

Connection pooling means .Net will hold open a few connections for you so that there is less overhead when you next require a connection. The CLR can just given you an already open (and cleaned up) connection which is a few hundred times quicker than reconnection to the database directly. When you call connection.Close() you are just handing back the connection to the pool for recycling.

A pool exists for every separate security context connection - this means that all connections made using the same SQL security will share a pool, whereas if you are using Windows authentication, each individual user connecting will have their own pool.

You are seeing issues because you are hitting 100 connections - the default maximum amount in the pool (the default minimum is 0). At this point the pool will not be able to provide another connection until one has been recycled, hence the application will hang or time out. You need to change your connection string to include a higher maximum number (and also look at cutting down your connections).