- 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.
Aw, this became an extremely good post. In thought I have to invest writing like this additionally – taking time and actual effort to make a good article… but what can I say… I procrastinate alot and no indicates manage to go done. https://royalcbd.com/how-to-make-cbd-gummies-at-home/
ReplyDeleteShop online for Moncler fake moncler jackets Clothing, Jackets & Coats fake moncler men jackets for Men and Women . Free Shipping & Free Returns for Loyallists or most Orders Over ...
ReplyDelete