Sunday, July 7, 2013

0 Tempdb usage: Work tables

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
  • cursors
  • 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

use tempdb;

RUN DBCC check db on master

dbcc traceon(1197);
dbcc checkdb(master);


Look up the tempdb log and page

use tempdb;
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.

dbcc traceon(3604);
dbcc page(2,1,200,3);


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.



dbcc traceon(3604);
dbcc page(2,1,192,3);


Page 192 is the data page ( page type 1) and it has the exactly same page id –2147483636 .



Post a Comment


SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates