SQL SERVER ACTIVITY MONITOR

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.
mahmood66Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mahmood66Author Commented:
Screenshot attached
process.bmp
0
mdagisCommented:
select * from sysprocesses
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Alternate, execute this system stored procedure.

sp_who2
0
DennisCACommented:
select * from sys.dm_exec_sessions

select * from sys.dm_exec_requests
0
ProjectChampionCommented:
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]  =           
  CASE                             
  WHEN r2.session_id IS NOT NULL AND r.blocking_session_id = 0 THEN '1'              
  WHEN r.session_id IS NULL THEN '1'               
  ELSE ''          
  END,      
  [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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.