2 Tempdb usage: Workfiles

 

• Work files is used for Hash join.
• Work files use uniform extend
• System objects are not required.
• No logging for the work files. ( m_flagbits = 0x20  in page header means allocation for the page was not logged)
• Use uniform extend with page type 6 .
• Workfiles space use workspace memory, if the SQL profiler event shows “Hash Warming” , then it means it split to the tempdb.

Performon counters for tracking work files. Workfiles created/sec does not mean it is using tempdb for splitting . Only when the Hash warming and pages allocated/sec increase .

extended allocated/sec counter does not include the workfiles

 

Below is the test to show the works files

 

sp_configure 'show advanced options', 1;

GO

RECONFIGURE

go

sp_configure 'max server memory', 100

go

reconfigure

go

CREATE DATABASE [summer_workfiles] ON  PRIMARY 

( NAME = N'summer_workfiles_data', FILENAME = N'C:\DATA\summer_workfiles_data.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON 

( NAME = N'summer_workfiles_log', FILENAME = N'C:\DATA\summer_workfiles_log.ldf' , SIZE = 2048KB  , FILEGROWTH = 10%);

ALTER DATABASE [summer_workfiles] SET RECOVERY SIMPLE WITH NO_WAIT;

use summer_workfiles;

create table t1 (c1 int, c2 char(5000) not null);

declare @x int

set @x = 0

while (@x < 100000)

begin

insert into t1 values (@x, 'I like daddy')

set @x = @x + 1

end;

 

use summer_workfiles

go

select * from t1 a

join t1 b

on a.c1 = b.c1

option (hash join)

go

 

When query first run, the page allocated/sec is high but no hash warming .

Once we see the hash warming from profiler , we can see the workfiles created per sec get constantly high value.