Sql-server – Debug Connection Pool Error

connection-poolingperformanceperformance-tuningquery-performancesql server

I have been getting the below error.

    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.
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

I know that this is a question already asked by some users and i have gone through most of those and i learned the usual root causes and the fixes.
I learning are below:

  • systems is unable to open new connections because connection pool max
    is reached.(default is 100 and it is set in the connection string)
  • Opened connections are not closed, called connection leaking
  • More than 100 concurrent connections are being used. Increase the max
    pool size in the connection string if we have more than 100
    simultaneous users.
  • 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.

When i did my research i learned that it connection pool is something which the application maintains.I talked to the application team and they say that it has never occurred earlier(for the last 4 or 5 years) and so it must be something with database server.
Our SQL server version earlier was SQL 2008 R2 and we migrated to SQL Server 2016 as the data became huge like 1.5 TB and the performance was bad.
After migrating to SQL Server 2016,and fixing some performance issues we were stable,but getting this connection pool error now and then.

How do i go about this connection pool error from the database side.

Thanks

Best Answer

I talked to the application team and they say that it has never occurred earlier...so it must be something with database server

Sounds about right =P

Our SQL server version earlier was SQL 2008 R2 and we migrated to SQL Server 2016

It's likely that, since your SQL Server upgrade, the performance of some of your queries has changed. This can lead to queries being blocked, or just running for longer (which in turn can cause this connection pooling problem, as you discovered in your investigation).

Have the developers, or users, notify you when they start getting these connection pool errors. This will give you a time range to look into.

If you have a monitoring tool, use it to look at the queries that were running during that time. This will give you the queries that need to be tuned.

If you don't have a monitoring tool, try logging sp_WhoIsActive to a table. There are good step-by-step instructions by Tara Kizer here: Logging Activity Using sp_WhoIsActive – Take 2

Related Question