HEMESH ORAWORLD: .NET connection Pooling

SQL Server does not keep the connection open after a "close" command, maybe a second or two.

When you close a connection in .NET, it does NOT close the physical connection to the SQL Server. It RELEASES it to the connection pool for other .NET applications to reuse. This is a function of .NET connection pooling.

SQL Server is NOT refusing connections at 100, the .NET connection pool is defaults to 100 connections.

Please see this write up on .NET connection pooling.  http://msdn.microsoft.com/en-us/library/aa175863(SQL.80).aspx

1. Does the processes with status sleeping and command as Awaiting Command are needed for the application even after the operations specific to them are complete?

-- No

2. Does these processes affect the performance?

-- It will if you have left thousand connections open. Connections need resources.

3. Does killing this process would affect the application connectivity?

-- You shouldn't have to go around killing these processes. I don't think it would affect. More work for you.

4. Why do we get several processes with status sleeping and command as Awaiting Command? Is this because of the front end programming errors?

-- Web app doesn't close the connection after the work has been done.

5. What should we do to avoid the increasing number of process with status sleeping and command as Awaiting Command?

-- Your web application needs to be configured or modified so that whenever it needs to do any work on SQL Server it does the following:

1. Opens a connection.

2. Do the SQL work e.g. execute a stored procedure or select some data back.