tempdb monitoring queries.

--Script to find historic tempdb usage by session.
SELECT TOP 5
*
FROM sys.dm_db_task_space_usage
WHERE session_id > 50
ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC ;

--Finding the top 5 sessions running tasks that use tempdb
SELECT *
FROM sys.dm_db_session_space_usage
WHERE session_id > 50
ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC ;

--Script to total tempdb usage by type across all files.

SELECT SUM(user_object_reserved_page_count) AS user_object_pages,
SUM(internal_object_reserved_page_count) AS internal_object_pages,
SUM(version_store_reserved_page_count) AS version_store_pages,
total_in_use_pages = SUM(user_object_reserved_page_count)
+ SUM(internal_object_reserved_page_count)
+ SUM(version_store_reserved_page_count),
SUM(unallocated_extent_page_count) AS total_free_pages
FROM sys.dm_db_file_space_usage ;

---To get text and plan
SELECT session_id,
text,
query_plan
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle) ;