There are three DMVs you can use to track tempdb usage:
sys.dm_db_task_space_usage
sys.dm_db_session_space_usage
sys.dm_db_file_space_usage
The first two will allow you to track allocations at a query & session level. The third tracks allocations across version store, user and internal objects.
The following example query will give you allocations per session:
SELECT
s.session_id AS [SESSION ID]
,DB_NAME(database_id) AS [DATABASE Name]
,HOST_NAME AS [System Name]
,program_name AS [Program Name]
,login_name AS [USER Name]
,status
,cpu_time AS [CPU TIME (in milisec)]
,total_scheduled_time AS [Total Scheduled TIME (in milisec)]
,total_elapsed_time AS [Elapsed TIME (in milisec)]
,(memory_usage * 8) AS [Memory USAGE (in KB)]
,(user_objects_alloc_page_count * 8) AS [SPACE Allocated FOR USER Objects (in KB)]
,(user_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR USER Objects (in KB)]
,(internal_objects_alloc_page_count * 8) AS [SPACE Allocated FOR Internal Objects (in KB)]
,(internal_objects_dealloc_page_count * 8) AS [SPACE Deallocated FOR Internal Objects (in KB)]
,CASE is_user_process
WHEN 1 THEN 'user session'
WHEN 0 THEN 'system session'
END AS [SESSION Type],
s.row_count AS [ROW COUNT]
FROM sys.dm_db_session_space_usage su INNER join sys.dm_exec_sessions s ON su.session_id = s.session_id
Below are the situation when SQL Server will use the temp file.
- usage of table variables or temporary tables
- sql server created intermediate resultsets as worktables in tempdb - usually for sorting purposes (usually is a sign of absent indexes/out-of-date statistics)
- sql server decided to pre-evaluate the resultset of table valued function and in this case it stores the data in tempdb
- recreating indexes with option SORT_IN_TEMPDB = ON