Current Activity DMVs

What is actually happening on the server right now? This collection covers sys.dm_exec_sessions, sys.dm_exec_connections, sys.dm_exec_requests and sys.dm_os_waiting_tasks, plus the two table-valued helpers sys.dm_exec_sql_text and sys.dm_exec_query_plan that turn handles into readable T-SQL and plans. It includes quick ways to pull the last statement for a session, the full request list with blocking info, a pair of scripts to create a deliberate block so you can test your own monitoring, and the classic "find the lead blocker" query built on sysprocesses.

/*
sys.dm_exec_sessions
sys.dm_exec_connections
sys.dm_exec_requests
sys.dm_os_waiting_tasks

sys.dm_exec_sql_text(sql_handle)
sys.dm_exec_query_plan(plan_handle)

*/



SELECT * FROM sys.dm_exec_sessions

SELECT * FROM sys.dm_exec_connections

SELECT * FROM sys.dm_exec_requests



-------------------------------
-- dm_exec_connections and dm_exec_sql_text
--
-- Get sql text query
-------------------------------
declare @sql varbinary(64) 
select @sql = most_recent_sql_handle from sys.dm_exec_connections where session_id = @@SPID
select * from sys.dm_exec_sql_text(@sql) 

select b.*, a.* from sys.dm_exec_connections a 
cross apply sys.dm_exec_sql_text(most_recent_sql_handle) b



-------------------------------
-- dm_exec_requests and dm_exec_query_plan
-- 
-- Get sql plan query
-------------------------------
select b.*, a.* from sys.dm_exec_requests a 
cross apply sys.dm_exec_query_plan(plan_handle) b



-------------------------------
-- dm_exec_requests
--
-- Current active processes using dm_exec_requests
-------------------------------
select a.blocking_session_id,a.last_wait_type,a.wait_resource,b.text,a.session_id,a.start_time,a.status,a.command,db_name(a.database_id) as database_name
,object_name(b.objectid,b.dbid) as object_name,a.user_id ,c.login_name 
,a.wait_time,a.cpu_time,a.total_elapsed_time,a.writes
,a.reads,a.logical_reads,a.granted_query_memory,a.nest_level,a.row_count,a.transaction_isolation_level
from sys.dm_exec_requests as a
cross apply sys.dm_exec_sql_text(sql_handle) as b inner join sys.dm_exec_sessions as c on a.session_id=c.session_id 
order by a.session_id



-------------------------------
-- dm_exec_sessions
--
-- Current active processes using dm_exec_sessions
-------------------------------
SELECT T.[text], P.[query_plan], S.[program_name], S.[host_name],
S.[client_interface_name], S.[login_name], R.*
FROM sys.dm_exec_requests R
INNER JOIN sys.dm_exec_sessions S 
ON S.session_id = R.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS T
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS P



-------------------------------
-- Create blocking for additional scripts
-------------------------------
-- run in query window 1
BEGIN TRAN
UPDATE Person.Contact SET Suffix = 'B' WHERE ContactID < 20
-- ROLLBACK or COMMIT

-- run in query window 2
SELECT * FROM Person.Contact WHERE ContactID < 20 



-------------------------------
-- dm_os_waiting_tasks 
--
-- get list of waiting tasks - http://blog.sqlauthority.com/2011/02/04/sql-server-dmv-sys-dm_os_waiting_tasks-and-sys-dm_exec_requests-wait-type-day-4-of-28/
-------------------------------
SELECT dm_ws.wait_duration_ms,
dm_ws.wait_type,
dm_es.status,
dm_t.TEXT,
dm_qp.query_plan,
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,
-- Optional columns
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
CROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1



-------------------------------
-- dm_os_waiting_tasks - tip 2732
-------------------------------   
SELECT   w.session_id
,w.wait_duration_ms
,w.wait_type
,w.blocking_session_id
,w.resource_description
,s.program_name
,t.text
,t.dbid
,s.cpu_time
,s.memory_usage
FROM sys.dm_os_waiting_tasks w
INNER JOIN sys.dm_exec_sessions s
ON w.session_id = s.session_id
INNER JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
WHERE s.is_user_process = 1
GO



-------------------------------
-- Current blocked processes
-------------------------------
select a.blocking_session_id,a.last_wait_type,a.wait_resource,b.text,a.session_id,a.start_time,a.status,a.command,db_name(a.database_id) as database_name
,object_name(b.objectid,b.dbid) as object_name,a.user_id ,c.login_name 
,a.wait_time,a.cpu_time,a.total_elapsed_time,a.writes
,a.reads,a.logical_reads,a.granted_query_memory,a.nest_level,a.row_count,a.transaction_isolation_level
from sys.dm_exec_requests as a
cross apply sys.dm_exec_sql_text(sql_handle) as b inner join sys.dm_exec_sessions as c on a.session_id=c.session_id 
where blocking_session_id <> 0
order by a.session_id



-------------------------------
-- Find lead blocking process
-------------------------------
SELECT
     spid
     ,sp.STATUS
     ,loginame   = SUBSTRING(loginame, 1, 12)
     ,hostname   = SUBSTRING(hostname, 1, 12)
     ,blk        = CONVERT(CHAR(3), blocked)
     ,open_tran
     ,dbname     = SUBSTRING(DB_NAME(sp.dbid),1,10)
     ,cmd
     ,waittype
     ,waittime
     ,last_batch
     ,SQLStatement       =
         SUBSTRING
         (
             qt.text,
             er.statement_start_offset/2,
             (CASE WHEN er.statement_end_offset = -1
                 THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                 ELSE er.statement_end_offset
                 END - er.statement_start_offset)/2
         )
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
     ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0