locked
how long must a sql server connection be idle before it is closed by the connection pool? RRS feed

  • Question

  • I have a client-server app that uses a SQLConnection ( .NET SqlClient Data Provider) to connect to sql server - pretty standard stuff. By default how long must connections be idle before the connection pooling manager will close the database connection and remove it from the pool? What setting if any controls this?

    This MSDN document only says

    > The connection pooler removes a connection from the pool after it has been idle for a long time, or if the pooler detects that the connection with the server has been severed.
    Thursday, July 16, 2009 11:49 AM

Answers

  • Pruning time is not configurable nor is it defined because you shouldn't rely upon it in most cases.  It is an implementation detail.  You should just assume that at some future time the connection will be automatically closed if it isn't used.

    > Correct.  Only when the connection is closed does this option apply.  It is not used otherwise.

    > The only option you have to ensure a quick termination is to not use connection pooling.

    If it is really critical for you then you might consider disabling the ADO.NET CP functionality and just manage it yourself.  Pooling isn't a hard technique to implement.  The biggest downside (besides more code to write and debug) is that you'll have to create custom connection objects to allow you to manage the pooling yourself rather than having ADO.NET do it. 

    Michael Taylor - 7/16/09
    http://p3net.mvps.org
    • Marked as answer by Zhi-Xin Ye Wednesday, July 22, 2009 3:19 PM
    Thursday, July 16, 2009 6:31 PM

All replies

  • Why does it matter?  You shouldn't rely on the CP to clean up connections for you.  You should always be sure to close connections when you are done with them.  You should not keep connections open for long periods of time.  It defeats the purpose of CPing.  Open the connection, do your work, and then close it.  If you follow that standard paradigm then how and when the CP formally closes the underlying connection is irrelevant. 

    As for your question it is mostly an implementation detail.  It is also partially controlled by the connection string itself (and the defaults used when building the string).  That varies by the provider.  The default settings in, say, SQL do not set a max timeout before the underlying connection is closed.  More importantly though it doesn't say that the connection will be silently closed but rather when the connection is closed by the client it then determines if the connection has been around long enough that it should be closed.  This is predominantly used for load balanced servers.  In other cases it doesn't really matter.

    As for automatic pruning the CP does seem to run about every 30 seconds but that is only after a delay of about 4 minutes.  The pruning basically just enumerates the pools and cleans up connections that have been around too long.  But again that is partially controlled by the provider.  All this is subject to change between versions and even service packs.  If you want to ensure that a pool is closed then you should use the new APIs added to SqlConnection.  If you aren't using SQL then you are going to have a harder time.  Technically each provider manages its own CP but all existing providers use the same base class where the CP is implemented so they behave the same.    In general you should just assume that once you open a connection to the DB then it'll remain open (on the database side) indefinitely.  If you don't want that then use the connection string to set a lifetime timeout but note that it might not take effect until the connection is closed in your code.

    Michael Taylor - 7/16/09
    http://p3net.mvps.org

    Thursday, July 16, 2009 1:43 PM

  • Why does it matter:
    As far as I know, my app does always close connections when it's finished with them. However, because of my app architecture the time until the CP actually closes the db connection could be significant.
    In my client-server app desktop clients connect directly to the database. There are a large number of clients that could connect, say 1000, but they do so only intermittently. If my app closes a connection when it's finished with it and the CP on each machine decides to keep the connection open in the pool then the total number of db connections could get significantly larger the longer it keeps 'idle' connections in the pool. One minute would be ok, 10 minutes would be bad, in my case. My app process remains running when it's finished with the connections, so the connection pool itself will remain running even when my app knows it's done with the connections.


    I'm not certain that this is something I really need to worry about: I raised my question because I'm looking at sp_who2 output showing many connections have been idle for 5-10 minutes, which I found surprising as I thought the CP would have closed them. So while it'd be nice to know exactly what the 'idle' time is, I'm more interested in knowing approximately what it is by default so I can tell whether my app appears to be behaving correctly. e.g if a connection is idle for 10 minutes does that definitely mean that it's not been closed correctly within my app code, or could it just be that the CP has decided not to prune it?

    I'd also like to know how to alter the connection string to set this 'idle' time to a certain duration. I expect the default is more suited to a 3-tier app model rather than the model I'm using.

    - Rory
    Thursday, July 16, 2009 2:52 PM
  • By default the connection won't be closed until the client shuts down.  I think the behavior you're seeing for your client is correct and appropriate.  If you have that many clients connecting to a single DB then you're going to have other problems.  You might have to consider setting up a server farm or something.  Unless you're running SQL Enterprise it isn't going to be happy with 1000 connections. 

    Nevertheless, for now, you can disable CP altogether for your app by adding "Pooling=false" to the string.

    Michael Taylor - 7/16/09
    http://p3net.mvps.org

    Thursday, July 16, 2009 3:08 PM
  • > By default the connection won't be closed until the client shuts down.

    I thought you said previously that the CP would prune after a few minutes?
    Why would it default to never closing connections when they have been 'closed' in code and then not used for a number of minutes?

    > Unless you're running SQL Enterprise it isn't going to be happy with 1000 connections. 

    If I'm able to close connections correctly there should never be more than a few dozen connections; it's only if they stick around for many minutes more than necessary that the connection numbers will get very high.

    > Nevertheless, for now, you can disable CP altogether for your app by adding "Pooling=false" to the string.

    Unfortunately (?) my app is written assuming CP exists - each time data access is needed it 'opens', uses, then 'closes' a connection. The CP is needed else there would be an actual connection created and closed for almost each procedure call or sql statement.


    So there's no way to say "don't keep idle connections in the pool longer than 1minute"?

    thanks,

    - Rory
    Thursday, July 16, 2009 3:15 PM
  • I said that the default behavior was to check to see if a connection should be pruned approximately every 30 seconds but there are any number of factors that can influence that.  Connection strings are the most influential.

    Having said all that the actual idle time is undefined.  Based upon the way the code seems to work the pruning occurs every 30 seconds after about 4 or 5 minutes of running.  Pruning is a 2 step process though.  The first time through the pool group is scanned to see if it has any active connections.  If it has none then it is marked for pruning.  The next time pruning runs it enumerates the groups scheduled for pruning and then removes them.  Thus, based upon the code, it would appear that inactive connections should be closed after about 1 minute of inactivity for the entire group.  A group is determined by the connection string and whether integrated security is used or not.

    In my testing I found that about 5 or 10 minutes after the last inactive connection goes away then the connection is dropped.

    Michael Taylor - 7/16/09
    http://p3net.mvps.org

    Thursday, July 16, 2009 4:05 PM
  • Check the docs for SqlConnection.ConnectionString.  Plenty of knobs available there, Connection Lifetime, Load Balance Timeout, Max Pool Size and Pooling.

    Hans Passant.
    Thursday, July 16, 2009 4:50 PM
  • Indeed (eg here ) , but as far as I see it nothing indicates what the idle time resulting in the CP closing connections is - neither what the default is, nor settings to change the default.

    > Connection Lifetime: 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 .

    Specifies time since creation, not time since last batch. Also suggests it's only checked when connections are returned to the pool, so if they're returned shortly after use and never used again this would mean they're not later closed. Could just be just the documentation is lacking though?

    It's possible that ConnectionLifetime will be checked periodically, but it's still not really what I want as it will result in connections being unnecessarily closed when they're regularly being used.

    > Load Balance Timeout: The minimum time, in seconds, for the connection to live in the connection pool before being destroyed.

    It's a minimum time, so increasing it should mean connections are open for a longer minimum time, but no way to make them closed sooner.


    It just seems weird that this behaviour isn't defined or configurable.

    Thursday, July 16, 2009 5:40 PM
  • Pruning time is not configurable nor is it defined because you shouldn't rely upon it in most cases.  It is an implementation detail.  You should just assume that at some future time the connection will be automatically closed if it isn't used.

    > Correct.  Only when the connection is closed does this option apply.  It is not used otherwise.

    > The only option you have to ensure a quick termination is to not use connection pooling.

    If it is really critical for you then you might consider disabling the ADO.NET CP functionality and just manage it yourself.  Pooling isn't a hard technique to implement.  The biggest downside (besides more code to write and debug) is that you'll have to create custom connection objects to allow you to manage the pooling yourself rather than having ADO.NET do it. 

    Michael Taylor - 7/16/09
    http://p3net.mvps.org
    • Marked as answer by Zhi-Xin Ye Wednesday, July 22, 2009 3:19 PM
    Thursday, July 16, 2009 6:31 PM