There are three different types of objects stored in tempdb.
• Internal Objects:
1. Intermediate runs for sort.
2. Intermediate results for hash join and hash aggregates.
3. XML variables or other large object (LOB) data type variables. (text, image, ntext, varchar(max), varbinary(max))
4. Queries that need a spool to store intermediate results.
5. Keyset cursors to store the keys.
6. Static cursors to store a query result.
7. Service Broker to store messages in transit.
8. INSTEAD OF triggers to store data for internal processing.
9. DBCC CHECK internally uses a query that may need to spool intermediate results.
10. Query notification and event notification use Service Broker.
• Version Store:
1. Snapshot Isolation / Read Committed Snapshot Islotaion
2. Triggers (After Triggers). Instead of triggers doesn’t generate versions.
3. MARS (Multiple Active Result Sets)
4. Index Rebuilds
• User Objects:
1. User defined tables and indexes
2. Local and global temporary tables, bulk insert and BCP intermediate results
3. Index rebuilds with “SORT IN TEMPDB” option.
Submitted questions and answers are subject to review and editing, and may or may not be selected for posting, at the sole discretion of w3Sniff.
Get Started
Comments
Leave a Comment