- 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;
sp_configure 'max server memory', 100
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 )
( 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;
create table t1 (c1 int, c2 char(5000) not null);
declare @x int
set @x = 0
while (@x < 100000)
insert into t1 values (@x, 'I like daddy')
set @x = @x + 1
select * from t1 a
join t1 b
on a.c1 = b.c1
option (hash join)
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.