Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Active Monitor Connections Expand / Collapse
Author
Message
Posted Tuesday, June 23, 2009 2:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 29, 2013 3:44 AM
Points: 25, Visits: 156
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
Post #739999
Posted Tuesday, June 23, 2009 5:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 4:32 PM
Points: 91, Visits: 351
It changes to sleeping when it has nothing to do - generally (and this is quite a massive generalization) you want sleeping connections.
Post #740077
Posted Tuesday, June 23, 2009 5:58 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 29, 2012 5:22 AM
Points: 260, Visits: 800
Sleeping connections = Connect Completed ; simply a client connection with no active query to the SQL Server





Cheers,
- Win.

" Have a great day "
Post #740078
Posted Tuesday, June 23, 2009 6:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 29, 2013 3:44 AM
Points: 25, Visits: 156
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.
Post #740100
Posted Tuesday, June 23, 2009 6:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 4:32 PM
Points: 91, Visits: 351
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
Post #740105
Posted Tuesday, June 23, 2009 6:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 4:32 PM
Points: 91, Visits: 351
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
Post #740106
Posted Thursday, June 25, 2009 6:54 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 29, 2012 5:22 AM
Points: 260, Visits: 800
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 "


  Post Attachments 
sysprocesses.xls (19 views, 22.00 KB)
Post #741768
Posted Thursday, June 25, 2009 2:59 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 17, 2014 4:32 PM
Points: 91, Visits: 351
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
Post #742218
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse