Active Monitor Connections

  • 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

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

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

    Cheers,
    - Win.

    " Have a great day "

  • 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.

  • 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

  • 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

  • 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 "

  • 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