Technical Article

Identify tempdb usage

,

Simply run this script against any tempdb to help identify any SQL or SQL Agent job that may be consuming large amounts of space in tempdb.

IF OBJECT_ID('tempdb..##TMP') IS NOT NULL
  /*Then it exists*/
  DROP TABLE ##TMP

IF OBJECT_ID('tempdb..##TMP2') IS NOT NULL
  /*Then it exists*/
  DROP TABLE ##TMP2

SELECT DISTINCT 
	p.spid, 
	j.name as JobName
INTO 
	##TMP
FROM
	master.dbo.sysprocesses p INNER JOIN msdb.dbo.sysjobs j ON master.dbo.fn_varbintohexstr(CONVERT(varbinary(16), job_id)) = SUBSTRING(REPLACE(PROGRAM_NAME, 'SQLAgent - TSQL JobStep (Job ', ''), 1, 34)


;WITH tempdb_space_usage AS (
    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,
	DES.login_name,
	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,
       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
INTO
	##TMP2
FROM 
	tempdb_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
	INNER JOIN sys.dm_exec_sessions DES ON TSU.session_id = DES.session_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;


SELECT 
	a.session_id, 
	a.login_name,
	ISNULL(b.JobName, 'N/A') AS [Job Name],	 
	a.[internal object MB space], 
	a.[internal object dealloc MB space], 
	a.[text], 
	a.[statement text], 
	a.[query_plan] 
FROM 
	##TMP2 a LEFT OUTER JOIN ##TMP b ON a.session_id = b.spid
WHERE
	(a.[internal object MB space] + a.[internal object dealloc MB space]) > 0

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating