Sunday, July 7, 2013

0 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.

image_thumb1[1]

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 .

image_thumb3

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

 

image

image

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.

image

image

0 comments:

Post a Comment

 

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