What is filling up your TempDB ?
To see the current the TempDB usage by active processes:
Code: Select all
WITH TempResultsCTE
AS
(SELECT S.login_name, S.session_id, TSU.exec_context_id,
CASE WHEN TSU.user_objects_alloc_page_count > TSU.user_objects_dealloc_page_count
THEN (TSU.user_objects_alloc_page_count - TSU.user_objects_dealloc_page_count)/128
ELSE 0
END AS user_objects_MB,
CASE WHEN TSU.internal_objects_alloc_page_count > TSU.internal_objects_dealloc_page_count
THEN (TSU.internal_objects_alloc_page_count - TSU.internal_objects_dealloc_page_count)/128
ELSE 0
END AS internal_objects_MB,
ER.sql_handle,
ER.plan_handle,
ER.statement_start_offset,
ER.statement_end_offset
FROM syS.dm_exec_requests ER
INNER JOIN syS.dm_exec_sessions S ON ER.session_id = S.session_id
INNER JOIN syS.dm_db_task_space_usage TSU ON ER.session_id = TSU.session_id
WHERE S.is_user_process = 1)
SELECT login_name, session_id, exec_context_id,
user_objects_MB + internal_objects_MB as total_objects_MB,
user_objects_MB, internal_objects_MB,
SUBSTRING(ST.text, (TR.statement_start_offset/2)+1,
((CASE TR.statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE TR.statement_end_offset
END - TR.statement_start_offset)/2) + 1) AS statement_text,
ST.text AS full_statement_text
FROM TempResultsCTE TR
CROSS APPLY syS.dm_exec_sql_text(TR.sql_handle) ST
CROSS APPLY syS.dm_exec_text_query_plan(TR.plan_handle, TR.statement_start_offset, TR.statement_end_offset) QP
WHERE TR.user_objects_MB + TR.internal_objects_MB > 0
ORDER BY TR.user_objects_MB + TR.internal_objects_MB DESC
Code: Select all
WITH IdleTempResultsCTE
AS
(SELECT S.login_name, S.session_id,
CASE WHEN SSU.user_objects_alloc_page_count > SSU.user_objects_dealloc_page_count
THEN (SSU.user_objects_alloc_page_count - SSU.user_objects_dealloc_page_count)/128
ELSE 0
END AS user_objects_MB,
CASE WHEN SSU.internal_objects_alloc_page_count > SSU.internal_objects_dealloc_page_count
THEN (SSU.internal_objects_alloc_page_count - SSU.internal_objects_dealloc_page_count)/128
ELSE 0
END AS internal_objects_MB,
ER.sql_handle,
ER.plan_handle,
ER.statement_start_offset,
ER.statement_end_offset
FROM syS.dm_exec_requests ER
INNER JOIN syS.dm_exec_sessions s ON ER.session_id = S.session_id
INNER JOIN syS.dm_db_session_space_usage SSU ON ER.session_id = SSU.session_id
WHERE S.is_user_process = 1 AND S.status = 'sleeping')
SELECT login_name, session_id,
user_objects_MB + internal_objects_MB as total_objects_MB,
user_objects_MB, internal_objects_MB,
--CONVERT(XML, QP.query_plan) AS query_plan,
SUBSTRING(ST.text, (TR.statement_start_offset/2)+1,
((CASE TR.statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE TR.statement_end_offset
END - TR.statement_start_offset)/2) + 1) AS statement_text,
ST.text AS full_statement_text
FROM IdleTempResultsCTE TR
CROSS APPLY syS.dm_exec_sql_text(TR.sql_handle) st
CROSS APPLY syS.dm_exec_text_query_plan(TR.plan_handle, TR.statement_start_offset, TR.statement_end_offset) QP
WHERE TR.user_objects_MB + TR.internal_objects_MB > 0
ORDER BY TR.user_objects_MB + TR.internal_objects_MB DESC
To build up a history of usage over a period of time you can:
- Create two stored procedures using the code above.
- Create third stored procedure to execute these procedures inserting the data into a table.
- Create a job to run the third stored procedure on a schedule every so many minutes.
