Worktable in SQL server

A worktable is an internal temporary table created by SQL Server in TempDB to store intermediate results during query execution such as sorting, grouping, hashing, and window functions. It is created during ORDER BY, GROUP BY, DISTINCT, HASH JOIN, window functions, or when memory spills occur. We cannot access it. They are internal objects, but their usage can be tracked via DMVs and execution plans.

Worktables are created when SQL Server needs intermediate storage during query execution, such as:

Ø  ORDER BY
Ø  GROUP BY
Ø  DISTINCT
Ø  HASH JOIN
Ø  HASH AGGREGATE
Ø  UNION
Ø  EXCEPT
Ø  INTERSECT
Ø  WINDOW FUNCTIONS (ROW_NUMBER, RANK, etc.) 

Ø  Spills when memory grant is insufficient

Common scenario where worktable is created

Operation	Reason
ORDER BY (no supporting index)	Sorting needed
GROUP BY	Aggregation storage
DISTINCT	Duplicate elimination
HASH JOIN	Build hash table
Window functions	Row buffering
Merge Join (sometimes)	Intermediate storage
Query spill	Memory shortage

It is invisible to user because it is an Internal Object and created on the TempDB.

Let’s see the demo

Creating a table and inserting few 1000K records into this table.

--creating a table CREATE TABLE WorkTable_Demo (     ID INT,     Amount INT );   --inserting 10000K records insert into WorkTable_Demo(ID,Amount) SELECT TOP 10000000     ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),     ABS(CHECKSUM(NEWID())) % 1000 FROM sys.objects a CROSS JOIN sys.objects b CROSS JOIN sys.objects c CROSS JOIN sys.objects d;

Table create and data inserted successfully.

Now we are running the query

set statistics io on set statistics time on SELECT * FROM WorkTable_Demo ORDER BY Amount;

Worktable is created.

Creates worktable for aggregation see below

SELECT Amount, COUNT(*) FROM WorkTable_Demo GROUP BY Amount;

In the above table we have not yet created index. Let’s create an index on this table.

CREATE INDEX IX_Sales_Amount ON WorkTable_Demo(Amount); GO

Index is created. Let’s run the above query and see.

SELECT Amount, COUNT(*) FROM WorkTable_Demo GROUP BY Amount;

Worktable is not created.

Worktables are dangerous because its Increase TempDB contention which Cause PAGELATCH_UP, PAGELATCH_EX and Increase I/O waits and Increase query latency due to that our query get slow.

Below are the points which we can use to Avoid Worktables

Ø  Create Proper Indexes
Ø  Reduce Hash Operations
Ø  Increase Memory Grant
Ø  Rewrite Queries

o   Remove unnecessary DISTINCT

o   Avoid ORDER BY unless required

o   Avoid SELECT * 

Ø  TempDB Tuning