SQL Server 2005 Activity Monitor

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » SQL Server 2005 Activity Monitor

SQL Server 2005 Activity Monitor

Rate Topic

Display Mode

Topic Options

Author

Message

SqlSavvy

Posted Wednesday, March 25, 2009 10:19 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, March 16, 2012 3:51 PM
Points: 83, 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.

Post #683444

umar iqbal

Posted Wednesday, March 25, 2009 10:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 12:13 PM
Points: 118, Visits: 274

use sp_who2

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

Crazy

Umar Iqbal

Post #683447

maechismo_8514

Posted Wednesday, March 25, 2009 10:36 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157

barunpathak (3/25/2009)

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

Post #683461

SqlSavvy

Posted Wednesday, March 25, 2009 10:44 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, March 16, 2012 3:51 PM
Points: 83, Visits: 300

How about Dormant status? What does it mean?

Post #683472

MANU-J.

Posted Thursday, March 26, 2009 1:31 PM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:56 PM
Points: 1,658, Visits: 8,564

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

Post #684540

adhiman

Posted Monday, June 18, 2012 4:13 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:03 AM
Points: 7, Visits: 90

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

Post #1317626

Lynn Pettis

Posted Monday, June 18, 2012 4:18 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 21,625, Visits: 27,468

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)

Post #1317628

SQLRNNR

Posted Monday, June 18, 2012 5:03 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1317642

« Prev Topic | Next Topic »

Permissions