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

Reference

• http://community.spiceworks.com/topic/254470-sql-database-intermittently-fails-load-from-asp-net-website