Sql-server – Connection pool Error

connection-poolingsql servertimeouttransaction

I am getting the below error from my .net application.

Source: xxx.Services.xxx.xxx.xxxx.xxxx.xxxxRequest
    ---
    System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

I have seen several posts regarding this connection pool error.I have gone through most of it and i understand why it is happening and the possible solutions from on the application side like closing the connections opened by application.
These is what i understood :

  1. Opened connections are not closed, called connection leaking.
  2. Morethan 100 concurrent connections are being used. Increase the max
    pool size in the connection string if we have more than 100
    simultaneous users.
  3. Slow queries or open transactions blocks new
    connections. When the connections are being held open by slow query
    execution/open transactions and instead of being reusing connections
    the new connection are open and eventually reaches the connection
    pool maximum.

How can i monitor this from the database side.I saw some suggestions like use sp_who or sp_who2 to see the existing sessions.When i execute sp_who2 when i see these errors,i see lots of active sessions,but how can i see the connection pool information and the connections which comes under the pool.
Questions:

  1. Is there any way to track the connections opened and relate it to a
    connection pool created from database side.

I have noticed that when the issue happens,the queries which normally runs faster ,goes slow.

  1. Is it expected?Because i am suspecting whether the slow queries are
    causing the connection pool errors.

Best Answer

Is there any way to track the connections opened and relate it to a connection pool created from database side.

Set the Application Name in your config and you can monitor from the server side in sys.dm_exec_sessions. You'll get a separate Connection Pool for each Connection String, so if you set the Application Name in the connection strings you can tell what pool each connection belongs to.

the queries which normally runs faster ,goes slow. Is it expected?

Yes. Before you start seeing timeouts getting connections, you will have waits of up to 30 seconds waiting for a pooled connection to become available. So it may be a delay in opening or re-opening a connection, rather than a slow query.

Or it could be an indirect correlation. Often the code path that leaks connections only happens when you get some other Exception. For instance blocking or resource exhaustion on the database is causing timeout Exceptions, which, due not using using blocks is causing the leaked connections. But that's just speculation.

BTW 99.99% of the time the cause is: "Opened connections are not closed, called connection leaking."

Related Question