TempDB Space Usage

Aaron Bertrand, a blogger on SQLBlog.com and keeper of www.aspfaq.com is one of those SQL Server MVPs whose material is always on my must-read list.  Here's a really useful query he developed to determine the space used by objects in TempDB:SELECT  SPID = s.session_id, s.\[host_name\], s.\[program_name\], s.status, s.memory_usage,

Kevin Kline, SolarWinds

October 22, 2007

1 Min Read
ITPro Today logo in a gray background | ITPro Today

Aaron Bertrand, a blogger on SQLBlog.com and keeper of www.aspfaq.com is one of those SQL Server MVPs whose material is always on my must-read list.  Here's a really useful query he developed to determine the space used by objects in TempDB:

SELECT 
SPID = s.session_id,
s.[host_name],
s.[program_name],
s.status,
s.memory_usage,
granted_memory = CONVERT(INT, r.granted_query_memory*8.00), 
t.text,  
sourcedb = DB_NAME(r.database_id), 
workdb = DB_NAME(dt.database_id), 
mg.*,
su.*
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_db_session_space_usage su 
ON s.session_id = su.session_id 
AND su.database_id = DB_ID('tempdb') 
INNER JOIN sys.dm_exec_connections c 
ON s.session_id = c.most_recent_session_id 
LEFT OUTER JOIN sys.dm_exec_requests r 
ON r.session_id = s.session_id 
LEFT OUTER JOIN (
SELECT
session_id,
database_id
FROM sys.dm_tran_session_transactions t
INNER JOIN sys.dm_tran_database_transactions dt
ON t.transaction_id = dt.transaction_id 
WHERE dt.database_id = DB_ID('tempdb') 
GROUP BY session_id, database_id
dt
ON s.session_id = dt.session_id
CROSS APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle,
c.most_recent_sql_handle)) t
LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg 
ON s.session_id = mg.session_id 
WHERE (r.database_id = DB_ID('tempdb') 
OR dt.database_id = DB_ID('tempdb')) 
AND s.status = 'running'
ORDER BY SPID;

You might consider writing your own query based upon dm_db_session_space_usage but this works quite well.  Thanks for sharing this, Aaron!

Enjoy,

-Kev

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like