SQL Server 2008 r2: What events/queries hit tempdb

We had a slowdown on a db server this morning. Checking the logs, the only thing that happened during the 2-minute slowdown worth noting was an object being created and deleted in tempdb over the span of about 3ms. I know that temporary tables and table variables would do this, but as far as I can tell none were used. Are there any other actions (e.g. nested selects) which might create a temporary table?

P.S. We've already figured out that it was most likely a log expansion that caused the slowdown, and the hit to tempdb was unrelated. I just wanted to ask for academic purposes and because I just cannot figure out what caused tempdb to be used.

To summarize Tempdb is used to store 3 types of objects:

-
user objects

• local temporary table (seen by one session)

• global temporary table (seen by all sessions)

• Table variables

-
internal objects used temporarily by SQL server during Query processing : operations such as sort , join, hash and cursors all require space in tempdb. see the sys.dm_db_session_space_usage DMV.

-
the version store :
The following features make use of the version store:

• Triggers

• Snapshot isolation or Read-Commited Snapshot isolation

• Online index operations

• Multiple Active Result Sets (MARS)

Please note that using the sys.dm_tran_version_store can be ressource intensive.

May I suggest that you check Troubleshooting insufficient disk space in tempdb for a few examples of how to use the DMV.

StackExchange Database Administrators Q#10604, answer score: 3