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;
checkpoint;

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.

image

Page c8 convert to page number 200.

 

image

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.

image

 

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 .

image

0 comments:

Post a Comment

 

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