SQL queries to find tempdb bloating

Question

I am trying to find out the queries which would have run and caused bloat to TempDB.

I am able to pull data like TempDB bloating on one particular day where it almost ate upto 300 GB of space, was somewhere in middle of last week.

As per post How to find the SQL statements that caused tempdb growth?, i can find below data but not sure how to interpret this as i can find host and login, but not the actual queries.

status  CPU TIME (in milisec)   Total Scheduled TIME (in milisec)   Elapsed TIME (in milisec)   Memory USAGE (in KB)    SPACE Allocated FOR USER Objects (in KB)    SPACE Deallocated FOR USER Objects (in KB)  SPACE Allocated FOR Internal Objects (in KB)    SPACE Deallocated FOR Internal Objects (in KB)  SESSION Type    ROW COUNT
sleeping    4428523 4618020 5418422 24  0   648 38349248    38351104    user session    1
sleeping    4411334 4578428 5389351 24  0   672 41238720    41240448    user session    1
sleeping    4601627 4820643 5631623 24  0   720 40119104    40121280    user session    1
sleeping    12887505    13667397    15209924    24  0   3528    46007808    46013184    user session    1
sleeping    4589595 4792873 5835456 24  0   528 39140800    39142528    user session    1
sleeping    6416738 6767615 7836356 24  0   1088    40406400    40408768    user session    1
sleeping    8626603 9072173 10416679    24  0   1488    43888640    43891584    user session    1
sleeping    4346345 4554241 5389904 24  0   976 39358208    39360128    user session    1
sleeping    11817344    12427608    13494857    24  0   3208    46888512    46894208    user session    1
sleeping    5182063 5435636 6242939 24  0   680 37975040    37977024    user session    1

Is there a way i can really find queries which caused almost 300 GB bloat last week.

Yes, am aware of 3rd party tools which would have save the info. But we do not have one on this. How can i find one if possible?

Answer 1

By default, SQL Server does not track and record queries that causing growth of TempDB. Moreover, even running SQL Profiler session would not help in this case.

However, extended events is an option. Brent Ozar has a blog post with an example:

Tracking tempdb growth using Extended Events

XE sessions are more lightweight than SQL Profiler traces. Moreover, your servers, perhaps, already have two or three running now, by default.

XE sessions can be setup to target a ringbuffer, so they can be completely in memory and catch queries that exceed some threshold, for instance 20 GB or more. In this case, risk that they will cause any issues are minimal.