We will take notes here regarding tempDB, the Verison Store and how tempDB is used.

Some of the content here is derived from https://thesqldude.com/tag/version-store/. I want to give him credit.

First, it is important to understand how tempDB is used and what is stored in the tempdb database. Basically you can classify these into 3 categories:

1. Internal Objects
2. Version Stores
3. User Objects

From a feature perspective, here are the features in SQL Server that use space from tempDB.

– Query tasks (i.e. sorting, joins etc.)
– Triggers
– Snapshot Isolation and Read Committed Snapshot Isolation (RCSI)
– MARS (multiple active record sets)
– Online index creation
– Temporary tables, table variables, and table-valued functions
– DBCC CHECKDB commands
– LOB parameters
– Cursors
– Service Broker and event notification
– XML and LOB variables
– Query notifications
– Database mail
– Index creation
– User-defined functions (UDF’s)

—
REF
Understanding Row Versioning-Based Isolation Levels
Monitoring tempdb space usage and scripts for finding queries which are using excessive tempdb space

Views – 1559