Workfile in SQL server

A Workfile is an internal temporary structure created in TempDB when SQL Server cannot perform certain query operations fully in memory and must spill intermediate results to disk. Workfiles are not user objects and are created & dropped automatically by the SQL Server query processor. Workfiles are mainly used by hash-based and sort-based query operators when memory grant is insufficient.

Workfiles are created mainly during memory-intensive query operations, such as:

Ø  Hash Operations

Ø  Sort Operations

Ø  Complex Query Plans

This situation is commonly known as “spill to TempDB”.

Workfiles are stored on the TempDB. Physical files named like “workfile_00000000000000xx.tmp”. These files are Session-scoped and auto-cleaned after query completion.

Workfiles are reduced by improving cardinality estimation, indexing to avoid hash/sort operations, controlling parallelism, and ensuring sufficient memory grants so operations can complete in memory.

Let’s see the demo

For the demo we are creating big tables.

USE Test_DB; GO DROP TABLE IF EXISTS FactSales; DROP TABLE IF EXISTS DimProduct; GO  CREATE TABLE DimProduct (     ProductID INT NOT NULL,     ProductName CHAR(100) );  CREATE TABLE FactSales (     SalesID BIGINT IDENTITY,     ProductID INT NOT NULL,     Amount DECIMAL(28,10),     Padding CHAR(200) ); GO

Inserting records into this table

-- Dimension (small-ish) INSERT INTO DimProduct SELECT TOP 50000     ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),     REPLICATE('B',100) FROM sys.objects a CROSS JOIN sys.objects b; GO -- Fact (BIG) INSERT INTO FactSales SELECT TOP 5000000     ABS(CHECKSUM(NEWID())) % 50000,     ABS(CHECKSUM(NEWID())) % 1000,     REPLICATE('B',200) FROM sys.objects a CROSS JOIN sys.objects b CROSS JOIN sys.objects c; GO

Records inserted successfully.

SET STATISTICS IO, TIME ON; GO   SELECT SUM(f.Amount) FROM dbo.FactSales f JOIN dbo.DimProduct d     ON f.ProductID = d.ProductID OPTION (HASH JOIN, MAXDOP 1); GO

It will create tempdb Spill.

Way to Reduce Workfiles

Ø  Add Supporting Index

Ø  Update Statistics

Ø  Avoid Hash Join

Ø  Remove Unnecessary DISTINCT & ORDER BY

Ø  Ensure Adequate Memory Grants