Have your SQL Servers ever gone mental on you all of a sudden, and you wish you could quickly see an overview of what was running at the time?
I just found these useful queries on a Spiceworks forum.
The first one will show you what is currently being executed on your server:
SELECT s.session_id AS SessionID, s.login_time AS LoginTime, s.[host_name] AS HostName, s.[program_name] AS ProgramName, s.login_name AS LoginName, s.[status] AS SessionStatus, st. AS SQLText, (s.cpu_time / 1000) AS CPUTimeInSec, (s.memory_usage * 8) AS MemoryUsageKB, (CAST(s.total_scheduled_time AS FLOAT) / 1000) AS TotalScheduledTimeInSec, (CAST(s.total_elapsed_time AS FLOAT) / 1000) AS ElapsedTimeInSec, s.reads AS ReadsThisSession, s.writes AS WritesThisSession, s.logical_reads AS LogicalReads, CASE s.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END AS TransactionIsolationLevel, s.row_count AS RowsReturnedSoFar, c.net_transport AS ConnectionProtocol, c.num_reads AS PacketReadsThisConnection, c.num_writes AS PacketWritesThisConnection, c.client_net_address AS RemoteHostIP, c.local_net_address AS LocalConnectionIP FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS st WHERE s.is_user_process = 1 ORDER BY ElapsedTimeInSec DESC
This next one can show blocking processes… you can use the blocking session ID to track down the other processes:
SELECT dm_ws.wait_duration_ms, dm_ws.wait_type, dm_es.status, dm_t.TEXT, dm_ws.session_ID, dm_es.cpu_time, dm_es.memory_usage, dm_es.logical_reads, dm_es.total_elapsed_time, dm_es.program_name, DB_NAME(dm_r.database_id) DatabaseName, dm_ws.blocking_session_id, dm_r.wait_resource, dm_es.login_name, dm_r.command, dm_r.last_wait_type FROM sys.dm_os_waiting_tasks dm_ws INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id CROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_t WHERE dm_es.is_user_process = 1