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