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


SQL Server 2005 Activity Monitor


SQL Server 2005 Activity Monitor

Author
Message
SqlSavvy
SqlSavvy
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 300
Can somebody give me brief description about Activity Monitor? Also what each of the status means? Sleeping, Runnable, Supspneded.

What is the easiest way to see if any process is hanging in the server?

Thanks much in advance.
umar iqbal
umar iqbal
SSC Veteran
SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)SSC Veteran (224 reputation)

Group: General Forum Members
Points: 224 Visits: 395
use sp_who2


activity monitor is slow. look for block by for any blocking

Crazy Alien

Umar Iqbal
maechismo_8514
maechismo_8514
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1830 Visits: 2228
barunpathak (3/25/2009)
Can somebody give me brief description about Activity Monitor? Also what each of the status means? Sleeping, Runnable, Supspneded.

What is the easiest way to see if any process is hanging in the server?

Thanks much in advance.


If a worker is at runnable it means that it is waiting for its turn on CPU. Many workers at Runnable state means a CPU hog.

If a worker is at Suspended state its waiting on a resource. Many workers at Suspended stats means a excessive blocking.

If a worker is in Sleeping state it means that it is not using any CPU resource
SqlSavvy
SqlSavvy
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 300
How about Dormant status? What does it mean?
MANU-J.
MANU-J.
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2912 Visits: 8766
a) Same as Sleeping, except Dormant also indicates that the SPID has
been reset after completing an RPC event. The reset cleans up
resources used during the RPC event. This is a normal state and the
SPID is available and waiting to execute further commands.


b) Same as "sleeping", except a "DORMANT" SPID was reset after
completing an RPC event from remote system (possibly a linked server).
This cleans up resources and is normal; the SPID is available to
execute. The system may be caching the connection. Replication SPIDs
show "DORMANT" when waiting.


Linked server connections from SQL Server 2000 clients rely on
connection pooling. After each batch is completed against the linked
server, the client instance sends the sp_reset_connection suffix
command to clean up the metadata information
After the sp_reset_connection stored procedure is performed, the SPID
goes into Dormant status on the remote linked server instance. You can
see the status of the SPID when you query the sysprocesses system
table.SELECT * FROM MASTER.DBO.SYSPROCESSES WHERE Status='Dormant'


If there are no additional queries to the same linked server, the
client instance of SQL Server disconnects the dormant connection to
the remote linked server instance after 5 minutes of inactivity.
However, the Audit Logout event is not produced, even though the
connection is disconnected successfully


When using linked servers there is no guaranteed that you get the same
connection nor same SPID between the execution of 2 consecutive
batches.
There is no way to force this behavior. By default SQL Server does not
use pooling when using linked servers.

Manu
adhiman
adhiman
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 152
If you can't get Activity monitor to come up.

I would suggest quering the sys views. something simiar to

sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
LEFT JOIN sys.dm_db_task_space_usage tsu
ON tsu.session_id = s.session_id
LEFT JOIN sys.dm_os_tasks t
ON t.session_id = tsu.session_id AND t.request_id = tsu.request_id
LEFT JOIN sys.dm_exec_requests r ON r.session_id = tsu.session_id AND r.request_id = tsu.request_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) TSQL
This way you can get a TotalPagesAllocated which can help you fiure out the spid that is taking all the server resources. There has lot of times when i cant even bring up activity monitor and use these sys views to see whats going on.

I would recommend you reading the following article. I got this reference from here
http://tsqltips.blogspot.com/2012/06/monitor-current-sql-server-processes.html
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39187 Visits: 38527
Three year old thread.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32182 Visits: 18551
Old maybe, but still a good thread to add some more tidbits:

Here is a blog on some good advice for activity monitor

http://jasonbrimhall.info/2011/07/28/activity-monitor-and-profiler/



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

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