DMV Version of sp_who2

by jasonstrate, posted in DMO, Performance Tuning & Tracing, SQL Server

I put together a presentation on Dynamic Management Views (DMVs) a few months back and one of queries I put together for that presentation was a version of sp_who2 that provided similar results but used DMVs instead.

The query has most of the same  columns as sp_who2  with some  additional items that I found useful to include:

  • reads: Reads recorded for the request.
  • writes: Writes recorded for the request.
  • wait_type: If the request is currently blocked, this column returns the type of wait.
  • wait_time: If the request is currently blocked, this column returns the duration in milliseconds, of the current wait.
  • wait_resource: If the request is currently blocked, this column returns the resource for which the request is currently waiting.
  • last_wait_type: If this request has previously been blocked, this column returns the type of the last wait.
  • transaction_isolation_level: Transaction isolation level of the session.
  • object_name: If the request called an object, the name of the object is listed here.
  • query_text: Currently executing SQL statement for the request.
  • query_plan: Execution plan for the currently executing request.
SELECT
es.session_id
,es.status
,es.login_name
,DB_NAME(er.database_id) as database_name
,es.host_name
,es.program_name
,er.blocking_session_id
,er.command
,es.reads
,es.writes
,es.cpu_time
,er.wait_type
,er.wait_time
,er.last_wait_type
,er.wait_resource
,CASE es.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 transaction_isolation_level
,OBJECT_NAME(st.objectid, er.database_id) as object_name
,SUBSTRING(st.text, er.statement_start_offset / 2,
(CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), st.text)) * 2
ELSE er.statement_end_offset END - er.statement_start_offset) / 2) AS query_text
,ph.query_plan
FROM sys.dm_exec_connections ec
LEFT OUTER JOIN sys.dm_exec_sessions es ON ec.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON ec.connection_id = er.connection_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_query_plan(plan_handle) ph
WHERE ec.session_id  @@SPID
AND es.status = 'running'
ORDER BY es.session_id

Since I’ve tended to use this when I’m checking into performance issues.  To make that easier, the query does remove the results for the current session and only returns running sessions.

I do recommend that anyone that wants to learn about DMVs try to build their own version of sp_who2.  It’s a good exercise and helps to learn what is in the DMVs and the relationships between them.