Why my ADO.net Connection was Closed but sp_who2 indicates "awaiting command" connections?

Question

We are facing serious problems with performance in the database. After making a lot of changes in the procedures, trying to minimize locks, we still have some problems of performance.

When I make a sp_who2 in the database, there are several connections in sleeping mode, "Awaiting command".

Maybe if I close these connections automatically after closing them, we could have a positive impact on the server performance.

And my question is: How to make connection pooling in ADO.NET close my connections when I call the close, instead of maintaining them in sleeping mode?

PS: I know handling performance is a large topic, we are analyzing more than just this.

Answer 1

You could disable the connection pool, but that would not increase performance.

The connection pool hangs on the the connections, so that the application doesn't have to create a new connection each time. Reusing a connection is a lot faster than opening a new one.

So, the sleeping connections is an indication that the connection pool works, and you should not try to get rid of it.

Answer 2

The point of connection pooling is to keep connections open so that when you want a new connection it is there. The reason to do this is that opening a connection is a costly business. Keeping a sleeping connection does not usually cost much.

If you want to close the connection fully then turn connection pooling off.

Answer 3

Well, one potential problem you could have it too many connection pools!

Connection pool will be based on the exact connection string - if two connection strings differ only by a space, they'll be considered different, and one connection pool will be created for each. So make sure all your connection strings are absolutely identical.

Also, one connection pool will be created per Windows identity - so if you use

server=myServer;Database=MyDatabase;integrated security=SSPI

then for each separate Windows user that will connect, a connection pool is being created.

See the relevant MSDN documentation for this (fourth paragraph from the top):

Connections are separated into pools by connection string, and by Windows identity when integrated security is used.

So in a heavy loaded production environment, it might be a better idea to use a single "application user" and have just a single connection pool:

server=myServer;Database=MyDatabase;user id=MyAppUser;pwd=MyAppUserPwd

If you make sure all client machines are all using this exact same connection string, only a single connection pool will be created.

Marc