Objects in Tempdb
By : Kasim Wirama, MCITP, MCDBA
Tempdb role has more functionality in SQL Server 2005/2008 than previous versions. In SQL 2005/2008, tempdb has 3 types of object, they are : user object, internal objects and version store. What are they?
User object is allocated for local and global temporary table. It is not limited to them, but also when you declare temporary table variable and run table valued function. You can get information about user objects by querying into SYS.OBJECTS. To get information about how much space is used for those user objects, you can query into SYS.PARTITIONS and SYS.ALLOCATION_UNITS catalogue view.
For Internal objects, you won’t be able to get information by running system-view query because their metadata is stored in memory. Inside internal objects, there are 3 basic internal objects ,i.e. : work table, work files and sort units. Work table is used when:
1. Your execution plan contains SPOOL operations
2. You work with XML and VARCHAR(max)
3. You use static or keyset cursor
4. You use service broker objects
5. You run DBCC CHECKDB or DBCC CHECKTABLE
Work files is used when there is HASH operator in query execution plan. And sort unit is used when there is SORT operator in execution plan, or explicit ordering such as ORDER BY, or rebuilding index. For rebuilding index you can have option either rebuilding in tempdb or rebuilding in corresponding user database.
For version store, they supports row level versioning technology such as read committed snapshot. They are used when :
1. AFTER trigger is used
2. MARS is referenced from client applications
3. DML is executed under snapshot based transactions.
4. Rebuilding index online whilst there are DML operations to the index.