SQL Script - How to find type of objects filling TempDB

Question: My TempDB is filling up quickly. I've checked the queries on Monitor SQL Server TempDB usage - SQL Server DBA , and can see which transactions are currently running. Do you have a query which can present a summary of the type of activity filling my TempDB?

Answers: Identifying what type of activity is filling the TempDB is the first step
Broadly speaking there are 3 categories of TempDB activity. Identifying which one is in highest usage gives you an immediate insight into TempDB activity.

Object Type -  User:Normally for  storing local\global temp tables, table variables,indices. Scope (user session or routine)
Object Type - Internal : Created for join work files , order by, sort, group by operators, hash .Scope (statement)
Object Type - Version store  : Mainly used at two scenarios. They are
- Online index build, version store  keeps multiple versions of the same row.
- If database has snapshot isolation enabled and if any transaction runs at snapshot isolation
 or Read Commited Snapshot Isolation (RCSI).

SELECT (SUM (user_object_reserved_page_count) * 8)/1024 AS usr_obj_MB ,
 (SUM (internal_object_reserved_page_count)* 8)/1024 AS internal_obj_MB ,
 (SUM (version_store_reserved_page_count) * 8)/1024 AS version_store_MB,
  (SUM (unallocated_extent_page_count) * 8)/1024 AS freespace_MB ,
 (SUM (mixed_extent_page_count) * 8)/1024 AS mixedextent_MB
FROM sys.dm_db_file_space_usage

Some basic guidelines on using this query are:
High  percentage of  usr_obj_MB suggests high temp table and table variable usage. Ensure you have enough disk space and consider whether the TempDB database is placed on an isolated disk to maximise throughput.
Higher percentage of internal_obj_MB suggests a high usage of worker files contributed by inefficient query plans. A query plan may have been effective with the initial data sets but over time as data sets grow , they can become inefficient.
Higher percentage of  version_store_MB indicates a higher usage of long transactions with snapshot isolation level in use

These are starting points . Depending on the initial findings , you can can apply analysis to identify specific session(s) which are causing the TempDB to fill up

Read More on managing TempDB

SQL Server – SORT IN TEMPDB and Sort Warnings - SQL Server DBA

SQL Server - TempDB performance and strategy checklist - SQL ...