Checking tempdb Usage

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!

One thought on “Checking tempdb Usage”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s