SQL Server Performance

Slow disconnects?

Discussion in 'Performance Tuning for DBAs' started by april10, Jan 3, 2003.

  1. april10 New Member

    Hi there,

    We have a typical IIS/ASP/ADO and SQL Server 7 setup.

    While profiling the database access of one of the ASP scripts we noticed that the duration for the Disconnect event is always between 103000 and 127000ms.

    This seems like a long time.

    Is the normal behaviour and if so won't this affect the effectiveness of connection pooling?

    tia


  2. satya Moderator

    First of all having IIS and SQL server on same machine will have performance degradation. You should find out the cause using PROFILER for those slow running/timed out queries. Then submit the trace to INDEX TUNING WIZARD for index recommendations which may fine tune the results execution.

    HTH

    Satya SKJ
  3. Argyle New Member

    The connection will hang around for a while when you use connection pooling, the web server will keep it open. Then when a new request comes IIS already has a connection open to the sql server and can use that, no need to do the timeconsuming handshake all over again. This is how connection pooling works.

    /Argyle

  4. april10 New Member

    satya: IIS and SQL server are not on the same machine. And it was using Profiler that we noticed the long duration of all Disconnect events. It has nothing to do we a particular query, imho.

    argyle: I understand that connections stick around for a while to be reusable, but that doesn't say anything about why a Disconnect would last aprox. 2 minutes, does it? Or do you think that the Profiler is telling us "OK, I've waited for 2 minutes - nobody was interested in this connection so I'm killing it"?


    thx for your responses
  5. bradmcgehee New Member

    I agree with Argyle, what you are seeing is a result of connection pooling. I would not worry about it.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  6. Argyle New Member

    april10: Yes, you got it correct, if noone requests the specific connection for 2 minutes (or whatever the timeout value is) it will be closed.

    /Argyle

Share This Page