-- http://msdn.microsoft.com/en-us/library/ms176029.aspx
;WITH all_task_usage
AS
(SELECT session_id,
SUM(internal_objects_alloc_page_count)
AS task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count)
AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id)
SELECT R1.session_id,
R1.internal_objects_alloc_page_count + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,
R1.internal_objects_dealloc_page_count + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count,
CASE
WHEN Rt.TEXT <> '' THEN (SELECT 1 AS tag,
NULL AS parent,
CHAR (13) + Rt.TEXT + CHAR (13) AS [Q!1!Q!CDATA]
FOR XML EXPLICIT,
TYPE)
END AS SQLText
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id
OUTER APPLY (SELECT TEXT
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) eh
WHERE er.session_id = R1.session_id) rt
-- WHERE r1.session_id = 123
ORDER BY R1.internal_objects_alloc_page_count + R2.task_internal_objects_alloc_page_count DESC