March 25, 2009 at 10:19 am
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.
March 25, 2009 at 10:22 am
use sp_who2
activity monitor is slow. look for block by for any blocking
:crazy: :alien:
Umar Iqbal
March 25, 2009 at 10:36 am
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
March 25, 2009 at 10:44 am
How about Dormant status? What does it mean?
March 26, 2009 at 1:31 pm
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
June 18, 2012 at 4:13 pm
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
June 18, 2012 at 4:18 pm
Three year old thread.
June 18, 2012 at 5:03 pm
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[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply