Created
March 18, 2022 21:25
-
-
Save marco-carvalho/e36718ffcfbfa0f0a24314dc15c99f36 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
;WITH task_space_usage AS ( | |
-- SUM alloc/delloc pages | |
SELECT session_id, | |
request_id, | |
SUM(internal_objects_alloc_page_count) AS alloc_pages, | |
SUM(internal_objects_dealloc_page_count) AS dealloc_pages | |
FROM sys.dm_db_task_space_usage WITH (NOLOCK) | |
WHERE session_id <> @@SPID | |
GROUP BY session_id, request_id | |
) | |
SELECT TSU.session_id, | |
TSU.alloc_pages * 1.0 / 128 AS [internal object MB space], | |
TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space], | |
EST.text, | |
-- Extract statement from sql text | |
ISNULL( | |
NULLIF( | |
SUBSTRING( | |
EST.text, | |
ERQ.statement_start_offset / 2, | |
CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END | |
), '' | |
), EST.text | |
) AS [statement text], | |
EQP.query_plan | |
FROM task_space_usage AS TSU | |
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK) | |
ON TSU.session_id = ERQ.session_id | |
AND TSU.request_id = ERQ.request_id | |
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST | |
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP | |
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL | |
ORDER BY 3 DESC, 5 DESC |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment