Post reply

Active Monitor Connections

  • June 23, 2009 at 2:59 am

    #201701

    hi all,

    When does a SPID status change to 'Sleeping' and what does that indicate? I'm assuming that after so many seconds or minutes have passed with no activity, it changes the status to Sleeping - is that correct? What is the length of time?

    Thanks,

    Thulasi

  • June 23, 2009 at 5:54 am

    #1013852

    It changes to sleeping when it has nothing to do - generally (and this is quite a massive generalization) you want sleeping connections.

  • June 23, 2009 at 5:58 am

    #1013853

    Sleeping connections = Connect Completed ; simply a client connection with no active query to the SQL Server

    Cheers,
    - Win.

    " Have a great day "

  • June 23, 2009 at 6:43 am

    #1013872

    hi Thanks for your quick reply..

    Actually in my production environment there will be always approx..170 Users connected to the database.

    out of those 170 i can able to see more than 100 is connection in sleeping mode..here i am planing to release that sleeping connections from active monitor.Can anyone help on this

    ?how can i able to achive this in SQL Server 2005.

  • June 23, 2009 at 6:50 am

    #1013877

    It is normally best to leave them. It is quite expensive for applications to create connections to sql so they generally have a pool of connections to use, if they had to create a new one every time they connected it would be quite slow.

    Ed

  • June 23, 2009 at 6:51 am

    #1013878

    also if you want to see which of the connections are actually doing something then run this query:

    select * from sys.sysprocesses where kpid 0

    ed

  • June 25, 2009 at 6:54 am

    #1015261

    Hi Edward,

    Interesting .... me too adding some info to this thread...

    Found some huge numbers in CPU..

    I simpy ran the query in my machine for testing. The attachment is the result set. Can you please check and suggest if any on that particular file if any changes to be done reg performance or any...

    Thanks,

    win

    Cheers,
    - Win.

    " Have a great day "

  • June 25, 2009 at 2:59 pm

    #1015634

    Hi Win,

    What is the actual problem, are you getting high cpu usage and / or slow queries??

    Can you detail the problem, when it started or if it has always happened?

    The actual rows themselves don't really say that much.

    Ed

Viewing 8 posts - 1 through 7 (of 7 total)

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