• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:


In the SQL Server Management studio 2005, there is Activity Monitor where we can see the information's like the attached screenshot...

Is it possible to get the same data using any query, and also some more details which is not in the Activity monitor.
1 Solution
mahmood66Author Commented:
Screenshot attached
select * from sysprocesses
Raja Jegan RSQL Server DBA & ArchitectCommented:
Alternate, execute this system stored procedure.

select * from sys.dm_exec_sessions

select * from sys.dm_exec_requests
As DennisCA has mentioned above look up DMVs in BOL. Pleanty of useful info there, so you can get the info you require withouth having to reinvent the wheel.
For example the following query will give you all the info about active sessions in all DBs with the current state of the task their running, waiting tie, waiting type, any blockage, resource usage (CPU, disk, RAM, etc) all in one place. Hope that helps... : )
SELECT      [Session ID]    = s.session_id,      
  [User Process]  = CONVERT(CHAR(1), s.is_user_process),     
  [Login]         = s.login_name,        
  [Database]      = ISNULL(db_name(r.database_id), N''),      
  [Task State]    = ISNULL(t.task_state, N''),      
  [Command]       = ISNULL(r.command, N''),      
  [Application]   = ISNULL(s.program_name, N''),      
  [Wait Time (ms)]     = ISNULL(w.wait_duration_ms, 0),     
  [Wait Type]     = ISNULL(w.wait_type, N''),     
  [Wait Resource] = ISNULL(w.resource_description, N''),      
  [Blocked By]    = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),     
  [Head Blocker]  =           
  WHEN r2.session_id IS NOT NULL AND r.blocking_session_id = 0 THEN '1'              
  WHEN r.session_id IS NULL THEN '1'               
  ELSE ''          
  [Total CPU (ms)] = s.cpu_time,      
  [Total Physical I/O (MB)]   = (s.reads + s.writes) * 8 / 1024,      
  [Memory Use (KB)]  = s.memory_usage * 8192 / 1024,      
  [Open Transactions] = ISNULL(r.open_transaction_count,0),      
  [Login Time]    = s.login_time,      
  [Last Request Start Time] = s.last_request_start_time,     
  [Host Name]     = ISNULL(s.host_name, N''),     
  [Net Address]   = ISNULL(c.client_net_address, N''),      
  [Execution Context ID] = ISNULL(t.exec_context_id, 0),     
  [Request ID] = ISNULL(r.request_id, 0),     
  [Workload Group] = N''  
  FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c 
  ON (s.session_id = c.session_id)  LEFT OUTER JOIN sys.dm_exec_requests r 
  ON (s.session_id = r.session_id)  LEFT OUTER JOIN sys.dm_os_tasks t 
  ON (r.session_id = t.session_id AND r.request_id = t.request_id)  
  LEFT OUTER JOIN   (      
    SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num      
  FROM sys.dm_os_waiting_tasks   ) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1  
  LEFT OUTER JOIN sys.dm_exec_requests r2 
  ON (r.session_id = r2.blocking_session_id)  
  ORDER BY s.session_id;

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now