3

We have one live ASP.Net application which is used by around 1000 concurrent users. In this application we have used connection pooling. There are 2 connection string defined in web.config with different name but we are calling same DB.

<add key="connection1" value="server=1.1.1.1;USER ID=**;pwd=**;database=test;Max Pool Size=500;Min Pool Size=50;Connection Timeout = 300; Pooling='true'" />

<add key="connection2" value="server=1.1.1.1;USER ID=**;pwd=**;database=test;Max Pool Size=500;Min Pool Size=50;Connection Timeout = 300; Pooling='true'"/>

In my application we are opening an closing the connection every where like below

try{
    if (connection.State == ConnectionState.Closed)
        {
            connection.Open();
        }
}
catch(Exception ex)
{
}
finally{connection.close()}

Now issue is when I am checking Database using sp_who2, I am seeing around 1000 or more than that sleeping session and for each record program name is ".Net sql server Provider". I gone through MSDN and found that if we are using connection pooling then connection keep opened till 4-8 minutes. So I am bit confused now, if I am using only 2 connection string with connection pool then why we are seeing so many sleeping connection, I am thinking it should be only 2? Can someone please help me in this situation and suggest what is correct? And how can we minimize sleeping sessions. Also not sure whether it's an application pool(IIS server) error or application error? Please help.

Thanks,

  • How many servers and .NET application pools are involved? You could have up to 200 connections per application pool so 1000 sleeping connections may be symptomatic of 5 servers after a burst of activity or long-running queries. These should be physically closed after a couple of minutes of inactivity. Consider using blocks as described in the documentation to ensure connections are properly managed. – Dan Guzman Aug 24 at 12:20
  • @DanGuzman thank you so much for your quick response. There is one server which has one application pool. In Asp.net application we have 2 connection string with pool enabled. we are physically closing all the connections in our application using Connection.Close(). But I am confused with one question which is.... If in our application there are 2 connections string with pool=true then why sql server is showing multiple sleeping sessions. As as per my knowledge if we use connection pooling then there should only be 2 sleeping sessions. Please confirm. – Vikas Aug 24 at 12:37
  • Close does not physically close the connection; it only returns it to the pool. The connection remains physically open and will show sleeping on the server. You could have up to 200 sleeping connections from the same host based on the information provided. Are all 1000 connections from the IIS server? – Dan Guzman Aug 24 at 12:50
  • @DanGuzman Yes, all are from same server. We are using Windows server, with 16GB RAM and IIS version is 7. I forgot to mention below string from connection string: Max Pool Size=500;Min Pool Size=50;Connection Timeout = 300; Pooling='true' – Vikas Aug 24 at 13:50
  • Possibly users are connecting to SQL Server using their own Windows credentials (delegation) instead of that of the app pool, in which case there will be a separate pool per user. Run SELECT host_name, original_login_name, COUNT(*) AS connection_count FROM sys.dm_exec_sessions GROUP BY host_name, original_login_name;. – Dan Guzman Aug 24 at 13:52
6

So I am bit confused now, if I am using only 2 connection string with connection pool then why we are seeing so many sleeping connection, I am thinking it should be only 2?

Min Pool Size=50 specifies number of connections established when the pool is created and will remain in the pool until the application pool is unloaded (including an IIS restart). With 2 distinct connection strings and security context, you will have at least 100 connections at all times and these will show as sleeping on SQL Server when not in use. The purpose of Min Pool is to ensure at least that number of connections are always available to the application and avoid the overhead of physically closing and opening connections that will be needed but at the costs mentioned below.

The Max Pool Size=500 specification allows up to 500 connections for each pool so you can have up to 1000 total connections if needed. The connections in excess of the Min Pool Size are acquired only when an unused connection is not available in the pool. The high number of connections you see with sp_who2 may be as a result of user activity, long-running queries, because connections are not closed/disposed in a timely manner, or pooled connections are still associated with a transaction context.

And how can we minimize sleeping sessions. Also not sure whether it's an application pool(IIS server) error or application error? Please help.

Not sure what you mean by IIS error but a large number of sleeping connections that were not due to query activity may indicate an application issue. All of the examples in the ADO.NET connection pool documentation employ a using block to ensure connections are properly closed and disposed without the need for app code to explicitly close the connection. I suggest you follow that practice with consistently throughout the application with both connections and transaction objects.

The sleeping connection overhead on the SQL server is additional memory and client side connection pool management. The query below will show how much memory is consumed by all SQL Server connections:

SELECT SUM(cntr_value) AS ConnectionMemoryKB
FROM sys.dm_os_performance_counters
WHERE counter_name = N'Connection Memory (KB)';

The ADO.NET connection pool documentation

0

Posting this answer because I'm not sure you already ruled out that other apps may be opening connections using same credentials to the same db.

A simple way to have a little more info about what's going on is to add Application Name to the connection string.

Some more detail here

Simply addApplication Name=Conn1; to the first string and Application Name=Conn2; to the second to have a quick way to link each sql connection to the relevant string and from there move gathering other info.

What happened to me is that on a busy intranet server, the result of poor choices was many apps using the very same credentials making almost impossible to tie each session to an application without auditing and this simple trick made sysadmin life somewhat easier with minimal effort and risk.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.