Activity Monitor Query – SQL Server

SQL Server Management Studio (SSMS) is the standard tool for managing any SQL infrastructure. It provides tools to configure, monitor and administer SQL instances. The Activity Monitor is a tool used to view the overall health of an instance and show all currently running processes. The below query expands on the information provided by the built in Activity Monitor. The query uses system tables to show the current processes, CPU usage, and blocking record information. This can be used to troubleshoot SQL issues and slowdowns, especially when working wit ERP applications like Dynamics AX. Stay tuned for more SQL queries focused on instance maintenance, query optimization and index definition.

use MicrosoftDynamicsAX
select 
   SessionId    = s.session_id, 
   UserProcess  = CONVERT(CHAR(1), s.is_user_process),
   LoginInfo    = s.login_name,   
   DbInstance   = ISNULL(db_name(r.database_id), N''), 
   TaskState    = ISNULL(t.task_state, N''), 
   Command      = ISNULL(r.command, N''), 
   App          = ISNULL(s.program_name, N''), 
   WaitTime_ms  = ISNULL(w.wait_duration_ms, 0),
   WaitType     = ISNULL(w.wait_type, N''),
   WaitResource = ISNULL(w.resource_description, N''), 
   BlockBy      = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
   HeadBlocker  = 
        case 
            -- session has active request; is blocked; blocking others
            when r2.session_id IS NOT NULL AND r.blocking_session_id = 0 then '1' 
            -- session idle; has an open tran; blocking others
            when r.session_id IS NULL then '1' 
            else ''
        end, 
   TotalCPU_ms   = s.cpu_time, 
   TotalPhyIO_mb = (s.reads + s.writes) * 8 / 1024, 
   MemUsage_kb   = s.memory_usage * 8192 / 1024, 
   OpenTrans     = ISNULL(r.open_transaction_count,0), 
   LoginTime     = s.login_time, 
   LastReqStartTime = s.last_request_start_time,
   HostName      = ISNULL(s.host_name, N''),
   NetworkAddr   = ISNULL(c.client_net_address, N''), 
   ExecContext   = ISNULL(t.exec_context_id, 0),
   ReqId         = ISNULL(r.request_id, 0),
   WorkLoadGrp   = N'',
   LastCommandBatch = (select text from sys.dm_exec_sql_text(c.most_recent_sql_handle)) 

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)
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) as st

where s.session_Id > 50 -- ignore anything pertaining to the system spids.
		AND s.session_Id NOT IN (@@SPID) -- hide this query
		and DB_NAME(r.database_id) = 'MicrosoftDynamicsAX'

order by s.session_id;