SQL Server Sleeping State – Increasing State Using ADO.NET

ado.netconnection-poolingsql serversql-server-2012

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,

Best Answer

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

Related Question