Spools, Worktables, Workfiles, Exchanges, and Hidden Tempdb Consumers

Learning outcomes

A DBA watches tempdb internal-object space climb even though no application code creates a #temp table. That does not mean SQL Server is leaking temporary tables. The optimizer and execution engine can create worktables, workfiles, sort runs, spool storage, cursor structures and other internal consumers that are invisible in application DDL.

Lab bootstrap

USE master;GOIF DB_ID(N'ServiceHubWorkspaceLab') IS NULL  CREATE DATABASE ServiceHubWorkspaceLab;GOALTER DATABASE ServiceHubWorkspaceLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubWorkspaceLab;GOIF SCHEMA_ID(N'lab12') IS NULL EXEC(N'CREATE SCHEMA lab12 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab12.WorkOrderFact',N'U') IS NULLBEGIN  CREATE TABLE lab12.WorkOrderFact  (    work_order_id bigint IDENTITY(1,1) NOT NULL,    customer_id int NOT NULL,    region_code char(3) NOT NULL,    status varchar(12) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    amount decimal(12,2) NOT NULL,    payload varchar(400) NULL,    CONSTRAINT PK_lab12_WorkOrderFact PRIMARY KEY CLUSTERED(work_order_id)  );  ;WITH n AS  (    SELECT TOP (60000)      ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT lab12.WorkOrderFact(customer_id,region_code,status,priority,opened_at,amount,payload)  SELECT CASE WHEN n <= 30000 THEN 1 ELSE 2 + n % 4999 END,         CASE WHEN n % 3=0 THEN 'N01' WHEN n % 3=1 THEN 'W02' ELSE 'E03' END,         CASE WHEN n % 19=0 THEN 'ESCALATED' WHEN n % 5=0 THEN 'CLOSED' ELSE 'OPEN' END,         CONVERT(tinyint,1 + n % 5),         DATEADD(minute,n,'2026-01-01T00:00:00'),         CAST(10 + (n % 20000)/10.0 AS decimal(12,2)),         CASE WHEN n % 13=0 THEN REPLICATE('x',350) ELSE REPLICATE('x',40) END  FROM n;  CREATE INDEX IX_lab12_RegionStatus    ON lab12.WorkOrderFact(region_code,status,opened_at)    INCLUDE(customer_id,priority,amount);END;GO

1. Internal objects are engine-created workspace

Microsoft’s sys.dm_db_task_space_usage documentation classifies worktables used by cursors/spools and temporary LOB storage, workfiles for hash operations, and sort runs as internal objects. This gives us a supported accounting boundary: the application may have created no temp table and still drive substantial tempdb allocation.

USE tempdb;GOSELECT @@SPID AS monitor_session;SELECT session_id,       user_objects_alloc_page_count,user_objects_dealloc_page_count,       internal_objects_alloc_page_count,internal_objects_dealloc_page_countFROM sys.dm_db_session_space_usageWHERE session_id=@@SPID;GO

For a target session, sample before and after a request, or join task usage to sys.dm_exec_requests while the request is active. The delta is more useful than a lifetime session total because connection pooling can keep sessions alive through many unrelated requests.

2. Sorts and hashes can consume tempdb without explicit temp objects

USE ServiceHubWorkspaceLab;GOSELECT customer_id,region_code,status,       COUNT_BIG(*) AS order_count,       SUM(amount) AS total_amount,       MAX(payload) AS widest_payloadFROM lab12.WorkOrderFactGROUP BY customer_id,region_code,statusORDER BY total_amount DESC,customer_id,region_code,status;GO-- In the same connection, inspect cumulative session allocation.SELECT session_id,       (user_objects_alloc_page_count-user_objects_dealloc_page_count)*8 AS net_user_kb,       (internal_objects_alloc_page_count-internal_objects_dealloc_page_count)*8 AS net_internal_kbFROM tempdb.sys.dm_db_session_space_usageWHERE session_id=@@SPID;GO

If all internal pages were deallocated by the time the second query runs, net usage may be small even though the request allocated many pages. For completed-work attribution, compare allocation and deallocation counters as well as net pages; for active-work attribution, sample sys.dm_db_task_space_usage while the query runs.

3. Spools are plan techniques, not error messages

A spool stores rows so another part of a plan can reuse or revisit them. An eager spool materializes its input before consumers proceed; a lazy spool stores rows as requested. SQL Server can use spools for performance, Halloween protection, recursive processing, uniqueness/rewind needs and other plan semantics. Some spool storage is represented by worktables in tempdb.

The deliberately wrong approach is “I saw a Table Spool, so I need an index to remove it.” An index may indeed remove repeated work in one case, but the spool may be protecting correctness or may be cheaper than repeated access. First read the operator properties, parent/child relationship, actual row counts, rebind/rewind behavior where exposed, and the whole plan.

USE ServiceHubWorkspaceLab;GOSELECT TOP (20)       cp.usecounts,qs.total_worker_time,qs.total_elapsed_time,       LEFT(st.text,300) AS sql_text,qp.query_planFROM sys.dm_exec_query_stats AS qsJOIN sys.dm_exec_cached_plans AS cp ON cp.plan_handle=qs.plan_handleCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qpWHERE st.dbid=DB_ID()  AND CONVERT(nvarchar(max),qp.query_plan) LIKE N'%Spool%'ORDER BY qs.total_elapsed_time DESC;GO

This query is a diagnostic convenience, not a production monitoring loop: converting many plans to strings is expensive. Query Store is often a better persisted evidence source for repeated investigations.

4. Exchanges and parallel plans: tempdb correlation is not causation

Parallel plans add exchange operators that distribute, repartition or gather rows between worker threads. Exchanges primarily coordinate row flow and buffers; seeing an exchange does not itself prove tempdb usage. But a parallel hash/sort plan can combine exchanges with grant pressure and internal workspace, so diagnose the operator chain rather than assigning all tempdb I/O to “parallelism.”

Likewise, a cursor can create a worktable; an index build can use tempdb if SORT_IN_TEMPDB is requested; versioning can consume traditional/PVS space; large object operations can create internal storage. That is why the first lesson taught category accounting before query tuning.

5. A request-level attribution pattern

USE tempdb;GOWITH tu AS(  SELECT session_id,request_id,         SUM(user_objects_alloc_page_count-user_objects_dealloc_page_count)*8 AS user_kb,         SUM(internal_objects_alloc_page_count-internal_objects_dealloc_page_count)*8 AS internal_kb  FROM sys.dm_db_task_space_usage  GROUP BY session_id,request_id)SELECT r.session_id,r.request_id,r.status,r.command,       r.wait_type,r.wait_time,r.cpu_time,r.total_elapsed_time,       tu.user_kb,tu.internal_kb,       LEFT(st.text,500) AS batch_textFROM sys.dm_exec_requests AS rJOIN tu ON tu.session_id=r.session_id AND tu.request_id=r.request_idCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS stWHERE r.session_id<>@@SPIDORDER BY tu.internal_kb DESC,tu.user_kb DESC;GO

Interpret this as a point-in-time view. A short request may finish between samples. Parallel workers are rolled into request-level totals. The current wait can change rapidly. For a recurring incident, pair sampling with Query Store, focused Extended Events, or your monitoring platform so the timeline survives after the request ends.

6. Wrong repair: “disable spools / parallelism globally”

There is no safe generic “turn off hidden tempdb” switch. Global changes such as suppressing parallelism, disabling optimizer features, forcing join types or changing instance memory can solve one observed symptom while damaging unrelated workloads. The safer sequence is: identify the consumer, inspect the query/plan, understand why the operator exists, correct estimates/query/index design if necessary, then validate concurrency and tempdb impact.

Production judgment

Treat “tempdb I/O increased” as an observation that needs classification. Keep per-file capacity/latency, per-category space, task/session allocation, plans, grants, waits and version-store state in the same incident timeline. A spool should be evaluated for both semantics and cost. Internal workspace is not waste by definition; it becomes a problem when the workload’s benefit/cost balance is poor or shared tempdb capacity/concurrency cannot sustain it.

Authoritative references

• sys.dm_db_task_space_usage — documented internal-object categories
• tempdb database — internal objects and space monitoring
• Execution plan operators — spool, sort, hash and exchange semantics
• Troubleshoot memory grant issues — spill and grant evidence
• Extended Events overview — focused event capture