DMV to track the temp file usage for SQL Server

There is three DMVs you can use for track tempdb usage:

Sys.dm_db_task_space_usage
Sys.dm_db_session_space_usage
Sys.dm_db_file_space_usage

The first and then 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 would 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 joins sys.dm_exec_sessions s on su.session_id = s.session_id

Below is the situation when SQL Server would use the temp file.

1. Usage of table variables or temporary tables
2. SQL Server created intermediate resultsets as worktables in tempdb-usually for sorting purposes (usually are a sign of AB Sent Indexes/out-of-date statistics)
3. SQL Server decided to pre-evaluate the resultset of the table valued function and in this case it stores the data in tempdb
4. Recreating indexes with option SORT_IN_TEMPDB = On