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

SQL Server 2005 Activity Monitor Expand / Collapse
Author
Message
Posted Wednesday, March 25, 2009 10:19 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Wednesday, March 25, 2009 10:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 2:30 PM
Points: 119, Visits: 346
use sp_who2


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




Umar Iqbal
Post #683447
Posted Wednesday, March 25, 2009 10:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 24, 2012 8:11 AM
Points: 1,097, Visits: 2,157
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




Post #683461
Posted Wednesday, March 25, 2009 10:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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
Posted Thursday, March 26, 2009 1:31 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, May 22, 2014 7:04 AM
Points: 1,688, Visits: 8,766
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
Posted Monday, June 18, 2012 4:13 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 11:48 AM
Points: 11, Visits: 106
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
Posted Monday, June 18, 2012 4:18 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:09 PM
Points: 20,739, Visits: 32,528
Three year old thread.



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
Posted Monday, June 18, 2012 5:03 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 17,825, Visits: 15,758
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
Post #1317642
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse