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.)
– 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
– Service Broker and event notification
– XML and LOB variables
– Query notifications
– Database mail
– Index creation
– User-defined functions (UDF’s)
Understanding Row Versioning-Based Isolation Levels
Monitoring tempdb space usage and scripts for finding queries which are using excessive tempdb space
Views – 1412