Click here to Skip to main content
15,886,060 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi Experts,

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.

C#
<add name="connection1" connectionString="Data Source=mySQLServer;Initial Catalog=myDatabase;Integrated Security=True;Connection Timeout=300;Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;" />

<add name="connection2" connectionString="Data Source=mySQLServer;Initial Catalog=myDatabase;Integrated Security=True;Connection Timeout=300;Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;" />


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

C#
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,

What I have tried:

I tried to check Application server IIS 7 as well. I've observed that as connection are increasing application is getting slow and we need to do recycle application pool.
Not sure about exact reason why sp_who2 is showing multiple sleeping session, if I am using only 2 pool then sleeping session should be 2 I think not sure though.
Posted
Updated 25-Aug-19 3:07am
Comments
Richard Deeming 27-Aug-19 15:18pm    
Your opening and closing code seems to suggest that you're sharing a single SqlConnection object around - at least within a single page, and possibly between requests.

Instead, you should create the connection object at the point when you need it, and wrap it in a using block. The connection pooling will take care of reusing the closed connections for you.
using (SqlConnection connection = new SqlConnection("..."))
{
    ... Code that uses the connection here ...
}

1 solution

Why don't you see if there is a relation between your "opens" and the "sleeping sessessions".

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


Maybe you need a better pattern / factory method so you can keep track of all the "opening and closing everywhere".
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900