Show running queries
This query provides information about queries currently in execution, useful typically when SQL Server is processing batches of long-running queries. It augments the information available from the SQL activity monitor with the text and line number of the current statement in each batch, includes information about tempdb utilisation, and provides a link to the query execution plan (if available).
Some additional notes:
- 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 the NOT LIKE '%9ow34ytghehl3q94wg%' clause is to exclude *this* query from the output!
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%'
), 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(
'[' + OBJECT_SCHEMA_NAME(r.objectid, r.database_id) + '].[' + OBJECT_NAME(r.objectid, r.database_id) + ']'
, LEFT(LTRIM(r.[text]), 128)) AS QueryBatch
, SUBSTRING(
r.[text]
, r.StatementStartOffset
, r.StatementEndOffset - r.StatementStartOffset
) AS CurrentStatement
, LEN(LEFT(r.[text], r.StatementStartOffset))
- LEN(REPLACE(LEFT(r.[text], r.StatementStartOffset), CHAR(10), ''))
+ 1 AS LineNumber
, 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
--*/ 
