SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Active Monitor Connections


Active Monitor Connections

Author
Message
trayalacheruvu
trayalacheruvu
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 181
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
edwardelliott
edwardelliott
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 Visits: 351
It changes to sleeping when it has nothing to do - generally (and this is quite a massive generalization) you want sleeping connections.
- Win.
- Win.
Say Hey Kid
Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)

Group: General Forum Members
Points: 682 Visits: 800
Sleeping connections = Connect Completed ; simply a client connection with no active query to the SQL Server

Cheers,
- Win.

" Have a great day "
trayalacheruvu
trayalacheruvu
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 181
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.
edwardelliott
edwardelliott
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 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
edwardelliott
edwardelliott
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 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
- Win.
- Win.
Say Hey Kid
Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)

Group: General Forum Members
Points: 682 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 "
Attachments
sysprocesses.xls (25 views, 22.00 KB)
edwardelliott
edwardelliott
Old Hand
Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)Old Hand (319 reputation)

Group: General Forum Members
Points: 319 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search