3

I've come across a high-traffic app which occasionally hits various scaling timeout issues I'm trying to diagnose. The app has these settings in the SQL connection string:

Min Pool Size=5;Max Pool Size=100;Connect Timeout=8;Connection Lifetime=30;

This of course means there are a minimum of 5 connections in the pool at all times, as confirmed by PerfMon.

But if I understand the Microsoft docs correctly, the Connection Lifetime setting is going to destroy any closed connection which has existed for more than 30 seconds:

When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime.

As these 5 (or more) connections are constantly used, am I right in thinking that none of them will ever live beyond 30 seconds? Thus every half a minute or so, my application is going all the way back to SQL to handshake 5+ brand new connections?

PerfMon suggests the number of connections never drops below 5, so I've no evidence of the above taking place (unless it's visually instant). Have I misunderstood?

In addition, this ancient article confirms the lifetime check is only performed on Close(), so this setting would have no benefit on addressing any connection leaks whatsoever?

FBryant87
  • 4,273
  • 2
  • 44
  • 72
  • The MinPoolSize keeps them alive; see the answer on this question https://stackoverflow.com/questions/19221687/why-do-we-need-to-set-min-pool-size-in-connectionstring – pfx Jun 06 '18 at 06:48
  • I agree it keeps a min number alive, but that question doesn't answer how MinPoolSize works in combination with ConnectionLifetime. According do the docs I quoted, those connections would still be killed after 30s, and re-created. – FBryant87 Jun 06 '18 at 08:29
  • The way I understand is that these don't get destroyed; they stay alive for the duration of the application. An other [answer](https://stackoverflow.com/questions/1136698/how-long-must-a-sql-server-connection-be-idle-before-it-is-closed-by-the-connect) links to an (old) [really technical article](http://www2.sys-con.com/itsg/virtualcd/dotnet/archives/0112/smith/index.html) mentioning: _closing connections until there are "Min Pool Size" connections left in the pool_. – pfx Jun 06 '18 at 09:02
  • Please update your question with the details of such an exception. If you have pooling issues, the most common one has to do with too few available ones: _"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."_ – pfx Jun 06 '18 at 09:28

0 Answers0