Many of the SQL Server function/features requires some form of the temporary storage when it is running . Instead of create the temporary table to hold those objects, SQL Server create the internal temporary table called work table to hold those purposes.
Those features includes but not limit,
- Service broker
- DBCC checkdb
- LOB variables
- XML document
- Merge join
Characteristic of work table
- Work tables is the internal storage for SQL Server development team.
- Work table is using mix extend.
- SQL Server cache the extend so it does need to reallocate them when it is needed unless trace flag 1197 is enable.
- Object id for work table is < 0
Below test is primary base on the Bob ward’s “Inside tempdb” session in the SQL SUMMIT 2011 .
Clean the tempdb log
RUN DBCC check db on master
Look up the tempdb log and page
select Operation, Context, [Transaction ID], AllocUnitId, AllocUnitName, [Page ID],
[Transaction Name], [Description]
from sys.fn_dblog(null, null)
The log shows the tempdb allocated the mix extend for the work table for the page c8.
Page c8 convert to page number 200.
Dump the page content.
Page 200 is IAM page ( page type 10) with negative object id. The actual data page for the work table is in the page 192.
Page 192 is the data page ( page type 1) and it has the exactly same page id –2147483636 .