Post reply

Is it truly important to clean up connections in a "sleeping" state?

  • December 22, 2015 at 1:31 pm

    #399499

    I've been searching here and MSDN looking for information about connections and trying to learn why it might be important to clean up sleeping connections coming from our web application.

    Our developers are using connection pooling from their web-application server farm. DBAs here are observing that particular queries are being run, they apparently return their results to the app but we find that the connection used for it shows up as 'active' but has a status of 'sleeping.'

    SELECT @@MAX_CONNECTIONS returns a value of 32,767.

    Other than just general tidiness, why would it be important to clean out those (apparently) stalled connections/sessions? It would seem like it would take a rather long time to fully consume all 32K worth.

    Any thoughts?

  • December 22, 2015 at 2:48 pm

    #1847596

    Unless there's an actual "connection leak" (extremely rare), there's usually nothing to worry about especially when there's connection pooling. If you do have a "connection leak", you won't have to look hard for it. It usually finds you.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 23, 2015 at 1:46 am

    #1847654

    Larry Kruse (12/22/2015)

    Other than just general tidiness, why would it be important to clean out those (apparently) stalled connections/sessions?

    It's not.

    Sleeping connections are doing nothing. Other than taking a couple MB of memory for the thread stacks, they're consuming no resources. Depending on the app, killing the connection on the DB side could actually cause problems if the app developers assume the connections are open and don't check.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply