Show running queries

The SSMS activity monitor provides a list of sessions with the text of the associated query batch. This script uses statement_start_offset and statement_end_offset to identify the currently executing statement within the batch and calculate its line number in the batch. Also includes a link to the selected execution plan, if available, and includes:

• start time and duration
• information about wait duration, type and blocking processes (if any)
• query memory grants.

WITH cte AS (
  SELECT
    r.session_id
  , r.request_id
  , r.database_id
  , r.start_time
  , DATEDIFF(SECOND, r.start_time, GETDATE()) AS run_duration_s
  , t.objectid
  , t.[text]
  , r.statement_start_offset/2 AS StatementStartOffset
  , CASE
      WHEN r.statement_end_offset > r.statement_start_offset THEN r.statement_end_offset/2
      ELSE LEN(t.[text])
    END AS StatementEndOffset
  , p.query_plan
  FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) t
    OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
    --OUTER APPLY sys.dm_exec_text_query_plan(r.plan_handle, r.statement_end_offset, r.statement_end_offset) p
  WHERE r.[sql_handle] IS NOT NULL
  AND t.[text] NOT LIKE '%9ow34ytghehl3q94wg%'  -- grep -v grep!
), spaceUsage AS (
  SELECT
    session_id
  , request_id
  , SUM(user_objects_alloc_page_count - user_objects_dealloc_page_count) / 128 AS UserObjMB
  , SUM(internal_objects_alloc_page_count - internal_objects_dealloc_page_count) / 128 AS InternalObjMB
  FROM sys.dm_db_task_space_usage
  GROUP BY
    session_id
  , request_id
)
SELECT
  r.session_id
, s.login_name
, DB_NAME(r.database_id) AS DbName
, COALESCE(
    QUOTENAME(OBJECT_SCHEMA_NAME(r.objectid, r.database_id)) + '.' + QUOTENAME(OBJECT_NAME(r.objectid, r.database_id))
  , LEFT(LTRIM(r.[text]), 128)) AS QueryBatch
, r.start_time as BatchStartDateTime
, CAST(r.run_duration_s / 3600 AS VARCHAR)
    + ':' + RIGHT('00' + CAST((r.run_duration_s % 3600) / 60 AS VARCHAR), 2)
    + ':' + RIGHT('00' + CAST((r.run_duration_s % 3600) % 60 AS VARCHAR), 2) AS RunTimeHMS
, SUBSTRING(
    r.[text]
  , r.StatementStartOffset
  , r.StatementEndOffset + 2 - r.StatementStartOffset
  ) AS CurrentStatement
, LEN(LEFT(r.[text], r.StatementStartOffset))
    - LEN(REPLACE(LEFT(r.[text], r.StatementStartOffset), CHAR(10), ''))
    + 1 AS [LineNo]
, wt.wait_type
, CAST((wt.wait_duration_ms / 1000) / 3600 AS VARCHAR)
    + ':' + RIGHT('00' + CAST(((wt.wait_duration_ms / 1000) % 3600) / 60 AS VARCHAR), 2)
    + ':' + RIGHT('00' + CAST(((wt.wait_duration_ms / 1000) % 3600) % 60 AS VARCHAR), 2) AS WaitTimeHMS
, wt.blocking_session_id AS BlockedBy
, mg.requested_memory_kb/1024 AS MemoryRequestedMB
, mg.used_memory_kb/1024 AS MemoryUsedMB
, u.UserObjMB AS [UserObjMB*]
, u.InternalObjMB
, r.query_plan
FROM cte r
  INNER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
  LEFT JOIN (
    SELECT TOP 1 WITH TIES *
    FROM sys.dm_os_waiting_tasks
    WHERE blocking_session_id <> session_id
    ORDER BY ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY wait_duration_ms DESC)
  ) wt ON wt.session_id = r.session_id
  LEFT JOIN spaceUsage u
    ON r.session_id = u.session_id
    AND r.request_id = u.request_id
  LEFT JOIN sys.dm_exec_query_memory_grants mg ON mg.session_id = r.session_id

• UserObjMB* typically relates to storage explicitly requested by the user (e.g. in temporary tables). The asterisk is intended to indicate that this is not always reliable – deferred deallocation in tempdb can make this appear to be higher than is the case.
• InternalObjMB relates to storage used by the database engine in evaluating the batch (e.g. hash buckets, temporary sort runs etc)
• The link to the query plan may be null if the plan is no longer in the cache or if the plan is too complex. In the latter case a text plan may still be available and can be obtained using the commented-out OUTER APPLY sys.dm_exec_text_query_plan.
• The purpose of NOT LIKE '%9ow34ytghehl3q94wg%' is to exclude this query from the output!

This narrower (featured on SQL Server Central!) omits:

• start time and duration;
• information about wait duration, type and blocking processes;
• query memory grants.

WITH cte AS (
  SELECT 
    r.session_id
  , r.request_id
  , r.database_id
  , t.objectid
  , t.[text]
  , r.statement_start_offset/2 AS StatementStartOffset
  , CASE
      WHEN r.statement_end_offset > r.statement_start_offset THEN r.statement_end_offset/2
      ELSE LEN(t.[text])
    END AS StatementEndOffset
  , p.query_plan
  FROM sys.dm_exec_requests r 
    CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) t
    OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
    --OUTER APPLY sys.dm_exec_text_query_plan(r.plan_handle, r.statement_end_offset, r.statement_end_offset) p
  WHERE r.[sql_handle] IS NOT NULL
  AND t.[text] NOT LIKE '%9ow34ytghehl3q94wg%'  -- grep -v grep!
), spaceUsage AS (
  SELECT 
    session_id
  , request_id
  , SUM(user_objects_alloc_page_count - user_objects_dealloc_page_count) / 128 AS UserObjMB
  , SUM(internal_objects_alloc_page_count - internal_objects_dealloc_page_count) / 128 AS InternalObjMB
  FROM sys.dm_db_task_space_usage 
  GROUP BY
    session_id
  , request_id
)
SELECT 
  r.session_id
, s.login_name
, DB_NAME(r.database_id) AS DbName
, COALESCE(
    QUOTENAME(OBJECT_SCHEMA_NAME(r.objectid, r.database_id)) + '.' + QUOTENAME(OBJECT_NAME(r.objectid, r.database_id))
  , LEFT(LTRIM(r.[text]), 128)) AS QueryBatch
, LEN(LEFT(r.[text], r.StatementStartOffset)) 
    - LEN(REPLACE(LEFT(r.[text], r.StatementStartOffset), CHAR(10), '')) 
    + 1 AS LineNumber
, SUBSTRING(
    r.[text]
  , r.StatementStartOffset
  , r.StatementEndOffset + 1 - r.StatementStartOffset
  ) AS CurrentStatement
, u.UserObjMB AS [UserObjMB*]
, u.InternalObjMB
, r.query_plan
FROM cte r
  INNER JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
  LEFT JOIN spaceUsage u
    ON r.session_id = u.session_id
    AND r.request_id = u.request_id
/*
UNION ALL
 
SELECT 
  9999
, NULL
, 'tempdb'
, CAST(SUM(unallocated_extent_page_count)/128 AS VARCHAR) + ' MB free'
, NULL
, NULL
, SUM(user_object_reserved_page_count) / 128
, SUM(internal_object_reserved_page_count) / 128
, NULL
FROM tempdb.sys.dm_db_file_space_usage
 
ORDER BY 1
--*/