The tempdb database in SQL Server holds a little bit of everything over its existence. There can be the temporary tables that you created, work tables like spools that SQL Server creates, version store data, and much more in tempdb at any time.
Is your tempdb having a hard time keeping up with the workload on your SQL Server? How can you find out what is using tempdb the most?
Show Me What You Got
I’ve had this script to run if anything starts acting suspicious with tempdb. I’ve seen it on a couple of sites but I think the original was from Pinal Dave.
The script is a long one:
SELECT st.dbid AS QueryExecutionContextDBID
,DB_NAME(st.dbid) AS QueryExecContextDBNAME
,st.objectid AS ModuleObjectId
,SUBSTRING(st.TEXT, dmv_er.statement_start_offset / 2 + 1, (
CASE
WHEN dmv_er.statement_end_offset = - 1
THEN LEN(CONVERT(NVARCHAR(MAX), st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset
) / 2) AS Query_Text
,dmv_tsu.session_id
,dmv_tsu.request_id
,dmv_tsu.exec_context_id
,(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts
,(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts
,dmv_er.start_time
,dmv_er.command
,dmv_er.open_transaction_count
,dmv_er.percent_complete
,dmv_er.estimated_completion_time
,dmv_er.cpu_time
,dmv_er.total_elapsed_time
,dmv_er.reads
,dmv_er.writes
,dmv_er.logical_reads
,dmv_er.granted_query_memory
,dmv_es.HOST_NAME
,dmv_es.login_name
,dmv_es.program_name
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er ON (
dmv_tsu.session_id = dmv_er.session_id
AND dmv_tsu.request_id = dmv_er.request_id
)
INNER JOIN sys.dm_exec_sessions dmv_es ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC
You can scroll through the results to find out if some cleanup is needed or if you just feel like being curious.
Hogging tempdb
What processes are the most likely to cause tempdb issues? Microsoft suggests these as causing possible contention problems:
- Repetitive create-and-drop operation of temporary tables (local or global).
- Table variables that use tempdb for storage.
- Work tables that are associated with CURSORS.
- Work tables that are associated with an ORDER BY clause.
- Work tables that are associated with a GROUP BY clause.
- Work files that are associated with HASH PLANS.
More to Come
It seems that I haven’t blogged much about tempdb so I think that might end up being an area of focus for the next few posts.
Thanks for reading!
Chad Callihan 
One thought on “Checking tempdb Usage”