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.

1. usage of table variables or temporary tables
2. sql server created intermediate resultsets as worktables in tempdb - usually for sorting purposes (usually is a sign of absent indexes/out-of-date statistics)
3. sql server decided to pre-evaluate the resultset of table valued function and in this case it stores the data in tempdb
4. recreating indexes with option SORT_IN_TEMPDB = ON

DMV to track the temp file usage for SQLServer,布布扣,bubuko.com

DMV to track the temp file usage for SQLServer