Session tempdb and log space usage information

session-db-tempdb-usage.sql

WITH cte_sessions AS
(
	SELECT
		exs.session_id
	,	exs.database_id
	,	exs.last_request_start_time
	,	exs.last_request_end_time
	,	exs.memory_usage
	,	ssu.user_objects_alloc_page_count +
		ssu.internal_objects_alloc_page_count AS tempdb_page_allocations
	,	ssu.user_objects_alloc_page_count +
		ssu.internal_objects_alloc_page_count -
		ssu.user_objects_dealloc_page_count -
		ssu.internal_objects_dealloc_page_count AS tempdb_page_current
	,	vss.reserved_page_count AS database_version_store_page_reserved
	,	exs.[status]
	,	exs.[host_name]
	,	exs.[program_name]
	,	exs.[login_name]
	FROM	sys.dm_exec_sessions AS exs
	LEFT
	JOIN	sys.dm_tran_version_store_space_usage AS vss
	  ON	exs.database_id = vss.database_id
	LEFT
	JOIN	sys.dm_db_session_space_usage AS ssu
	  ON	exs.session_id = ssu.session_id
	 AND	ssu.database_id = 2 -- tempdb
)
, cte_requests AS
(
	SELECT
		con.session_id
	,	SUM(tsu.user_objects_alloc_page_count +
		tsu.internal_objects_alloc_page_count) AS tempdb_page_allocations
	,	SUM(tsu.user_objects_alloc_page_count +
		tsu.internal_objects_alloc_page_count -
		tsu.user_objects_dealloc_page_count -
		tsu.internal_objects_dealloc_page_count) AS tempdb_page_current
	,	STRING_AGG(txt.[text], '|') AS statements
	--,	MAX(txt.[text]) AS statements	-- performance compromise pre 2017 instead of using FOR XML to aggregate 
	,	COUNT(*) AS num_statments
	FROM	sys.dm_exec_connections AS con
	LEFT
	JOIN	sys.dm_exec_requests AS req
	  ON	con.connection_id = req.connection_id
	LEFT
	JOIN	sys.dm_db_task_space_usage AS tsu
	  ON	req.session_id = tsu.session_id
	 AND	req.request_id = tsu.request_id
	 AND	tsu.database_id = 2
	OUTER
	APPLY	sys.dm_exec_sql_text(con.most_recent_sql_handle)  AS txt
	GROUP BY con.session_id
)
SELECT
	esx.session_id, DB_NAME(esx.database_id) AS [database_name]
,	COALESCE(esx.last_request_end_time, esx.last_request_start_time) AS last_request_time
,	CAST(esx.tempdb_page_current + ISNULL(req.tempdb_page_current, 0) AS BIGINT) * 8192 / 1024. / 1024. AS tempdb_page_current_MB
,	CAST(esx.tempdb_page_allocations + ISNULL(req.tempdb_page_allocations, 0) AS BIGINT) * 8192 / 1024. / 1024. AS tempdb_page_allocations_MB
,	CAST(esx.database_version_store_page_reserved as BIGINT) * 8192 / 1024. / 1024. AS databse_version_store_page_reserved_MB
,	esx.[status], esx.[host_name], esx.[program_name], esx.[login_name]
,	CAST(esx.memory_usage AS BIGINT) * 8192 / 1024. / 1024. AS memory_usage_MB
,	req.num_statments, req.statements
FROM	cte_sessions AS esx
LEFT
JOIN	cte_requests AS req
  ON	esx.session_id = req.session_id
WHERE
		(esx.[status] <> N'sleeping'
  OR	EXISTS (
		SELECT	*
		FROM	sys.dm_tran_active_transactions AS atx
		JOIN	sys.dm_tran_session_transactions AS stx
		  ON	atx.transaction_id = stx.transaction_id
		WHERE	stx.session_id = esx.session_id)
		)
--OR esx.session_id = 58
ORDER BY tempdb_page_current_MB