Introduction

A recursive query in SQL Server helps manage hierarchical data like categories, folders, and organization charts. We use a recursive query to handle parent-child relationships in product structures or folder structures. For example, a product hierarchy often follows this structure:  category → product → model → variant → year → month → date. Every recursive query consumes memory, and when it goes deep, it can spill into tempdb, causing performance hits.

In this tutorial, we explore how recursive query perform in SQL Server, with a focus on their impact on memory and tempdb usage. We'll compare three widely used techniques for implementing recursive logic:

• Recursive Common Table Expressions (CTEs)
• WHILE loops using Temp Tables (#TempTable)
• WHILE loops using Table Variables (@TableVar)

Throughout the article, we’ll analyze:

• How each recursive query method approach uses tempdb
• When tempdb is hit vs. when memory is used
• How to track recursive query behavior using DMVs and execution plans

How Tempdb Works

Tempdb handles temporary objects and operations such as:

• Sorting, hashing, joining
• Version store for snapshot isolation
• Temp tables and table variables
• Intermediate results for recursive CTEs

When available memory is insufficient, SQL Server spills data to tempdb. This impacts performance and can lead to unplanned file growth.

SQL Server tracks tempdb allocations in two categories: Internal objects (e.g., worktables, hash joins) and User objects (e.g., temp tables, table variables). We’ll use Dynamic Management Views (DMVs) like sys.dm_db_session_space_usage and sys.dm_db_task_space_usage to measure these.

Creating Sample Hierarchical Data

To properly test recursion, we need a table containing parent-child relationships, similar to an organization structure, product categories, or folder hierarchy. The code below creates a tree-like structure in sql server table with 1 million rows and with a random depth, simulating real-world recursion.

DROP TABLE IF EXISTS HierarchyBig;
CREATE TABLE HierarchyBig (
  ID       INT PRIMARY KEY,
  ParentID INT NULL,
  Name     NVARCHAR(100)
);

-- Insert Root Nodes (Top-Level Parents)
INSERT INTO HierarchyBig (ID, ParentID, Name)
VALUES
  (1, NULL, 'Node 1'),
  (2, 1, 'Node 2'),
  (10, 1, 'Node 10');

-- Insert Child Nodes
INSERT INTO HierarchyBig (ID, ParentID, Name)
SELECT ID + 100, ID, CONCAT('Node ', ID + 100)
FROM HierarchyBig
WHERE ID < 50;

-- Insert 1 Million+ Hierarchical Rows
SET NOCOUNT ON;

WITH Numbers AS (
  SELECT TOP 1000000
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
  FROM master.dbo.spt_values a
  CROSS JOIN master.dbo.spt_values b
)
INSERT INTO HierarchyBig (ID, ParentID, Name)
SELECT
  N + 1000, -- Ensure unique ID beyond existing
  CASE
    WHEN N = 1 THEN NULL
    ELSE ABS(CHECKSUM(NEWID())) % (N - 1) + 1
  END,
  'Node ' + CAST(N AS NVARCHAR(10))
FROM Numbers;

Tracking tempdb and Memory Usage

To measure how much tempdb memory a query uses, capture session stats before and after running it using sys.dm_db_session_space_usage. This shows how many pages SQL Server allocated for user objects (like temp tables, table variables) and internal structures (like hash joins, recursion). Run the view after your recursive query by passing spid of respective session, and this reveals how much space was used and whether it was cleaned up. It’s a simple way to compare methods like recursive CTEs vs temp tables without guessing.

SELECT 
  session_id,
  user_objects_alloc_page_count AS UserAllocPages,
  user_objects_dealloc_page_count AS UserDeallocPages,
  internal_objects_alloc_page_count AS InternalAllocPages,
  internal_objects_dealloc_page_count AS InternalDeallocPages
FROM sys.dm_db_session_space_usage
WHERE session_id = @@SPID;

Implementing Recursive Queries by Three Methods

Now, let’s test recursion using three methods giving the same results of parent-child relationship and log their performance and tempdb usage in each scenario.

Scenario 1: Recursive CTE (MAXRECURSION)

This query uses a recursive Common Table Expression (CTE) to fetch parent-child relationships from a table and organise them into different hierarchy levels. It starts by selecting top-level parents (ParentID IS NULL) and then recursively joins their children(level 1), grand children(level 2), and so on. Each entry is labeled with a Level value to indicate how deep it sits in the hierarchy. The MAXRECURSION 100 option ensures it doesn't run endlessly, or maximum fetch up to 100 level of relationships if depth of relations are very huge. We run this multiple times, capturing tempdb usage before and after each execution to measure how much memory is used by the recursion in different workload conditions.

WITH RecursiveTree
AS ( SELECT
       ID
     , ParentID
     , Name
     , 1 AS Level
     FROM HierarchyBig
     WHERE ParentID IS NULL
     UNION ALL
     SELECT
       h.ID
     , h.ParentID
     , h.Name
     , t.Level + 1
     FROM
       HierarchyBig h
       INNER JOIN RecursiveTree t
         ON h.ParentID = t.ID)
SELECT * FROM RecursiveTree
OPTION (MAXRECURSION 100);

Here is the query plan. Notice the Index Spool, which is circled.

Below screenshot showing 14 minutes execution via recursive cte

The screenshot below shows that the tempdb space allocated for the recursive CTE is minimal, and while this usage may increase with larger data volumes, what's particularly interesting is that both allocation and deallocation happen automatically. SQL Server manages this process internally, ensuring that memory is cleaned up immediately after execution without requiring any manual intervention.

Scenario 2: WHILE Loop with Temp Table (#TempTable)

In this method, we use a WHILE loop to process similar parent-child and subsequent hierarchy levels and store results in a #TempTable. Since temp tables are created directly inside tempdb, this approach explicitly uses SQL Server’s temporary storage to hold intermediate results. We use while loop to iterate each level of relations step by step.  The results of this step will be same as scenario 1.

DROP TABLE IF EXISTS #Hierarchy;
CREATE TABLE #Hierarchy
  (ID INT PRIMARY KEY,
   ParentID INT,
   Name NVARCHAR(100),
   Level INT);

--  Seed root nodes (Level 1)
INSERT INTO #Hierarchy (ID, ParentID, Name, Level)
SELECT ID, ParentID, Name, 1
FROM HierarchyBig
WHERE ParentID IS NULL;  -- This identifies top-level nodes

DECLARE @Level INT = 1, @RowCount INT = 1;

--Recursive WHILE loop
WHILE @RowCount > 0
BEGIN
  INSERT INTO #Hierarchy (ID, ParentID, Name, Level)
  SELECT h.ID, h.ParentID, h.Name, @Level + 1
  FROM HierarchyBig h
  INNER JOIN #Hierarchy t ON h.ParentID = t.ID
  WHERE t.Level = @Level;

  SET @RowCount = @@ROWCOUNT;
  SET @Level = @Level + 1;
END;

--  Final result
SELECT * FROM #Hierarchy
ORDER BY Level, ID;

Each test run completed in under 1 second, showing this is faster than recursive cte. As expected, we saw User Allocations in tempdb increase — because temp tables consume user object space. The amount of space used depended on how much data was processed. We can confirm that the same number of rows were returned, but in just 1 second, as shown in the screenshot below.

Importantly, when the #TempTable was dropped after execution, the space was immediately deallocated, and this was reflected in the user_objects_dealloc_page_count. This highlights a key point for real-world applications: if you use temp tables regularly, you must ensure that they’re properly cleaned up, or else they’ll keep consuming memory.

Monitoring alloc and dealloc counters helps confirm whether your application is efficiently releasing tempdb space — a best practice especially under heavy workloads.

For our sample of data allocated pages in each run for temp table method was 120. So after 3rd execution, until we had not deleted #Hierarchy table, allocated pages were still there. The below screenshot reveals allocated and deallocated pages(manual in temp table method by drop table command) in the table table process:

Once we executed DROP Table #Hierarchy , all pages were deallocated.

Scenario 3: WHILE Loop with Table Variable (@TableVar)

In this method, we used a WHILE loop along with a table variable (@TableVar) to process hierarchical data. Although table variables are often thought to stay entirely in memory, our observations showed that they do use tempdb — specifically the user object space — during execution.

In each test run, we saw clear user page allocations recorded in tempdb, similar to temp tables. However, the key difference was that the same amount of space was automatically deallocated right after execution — no manual cleanup needed. This shows that SQL Server internally manages table variables with precise memory hygiene.

Execution time was also consistently fast — just like the temp table method — often completing in under a second. So while table variables do touch tempdb, they behave efficiently and cleanly, making them a solid choice when you want quick performance with zero cleanup overhead.

DECLARE @Hierarchy TABLE
  (ID       INT PRIMARY KEY
 , ParentID INT
 , Name     NVARCHAR(100)
 , Level    INT);
DECLARE
  @Level INT = 1
, @RowCount INT = 1;
WHILE @RowCount > 0
BEGIN
  INSERT INTO @Hierarchy
  SELECT
    h.ID
  , h.ParentID
  , h.Name
  , @Level + 1
  FROM
    HierarchyBig h
    INNER JOIN @Hierarchy t
      ON h.ParentID = t.ID;
  SET @RowCount = @@ROWCOUNT;
  SET @Level = @Level + 1;
END;
SELECT * FROM @Hierarchy;

We can confirm that the same number of rows were returned, but in just 1 second likewise temp table method, as shown in the screenshot below.

Also allocation and deallocation of tempdb pages were automatic in this method:

Final Conclusion

We used all three methods—Recursive CTE, WHILE loop with Temp Table, and WHILE loop with Table Variable—to process hierarchical data successfully., but their behavior in terms of performance and tempdb usage varied significantly.

The Recursive CTE, while elegant, consistently took the longest to execute (~14 minutes for 100 iterations) and relied heavily on internal processing without showing significant tempdb allocations. This highlights its inefficiency for deeper recursion.

The Temp Table method was the most stable and predictable. It showed clear user space allocation in tempdb based on data volume and completed in under a second. Deallocation occurred only when the table was explicitly dropped, making cleanup a developer responsibility.

The Table Variable method behaved similarly in terms of speed, but its memory usage was cleaner. It did allocate user pages in tempdb, but SQL Server automatically deallocated them right after execution—making it efficient and maintenance-free for moderate workloads.

In summary, for shallow to moderate recursion, temp tables and table variables offer excellent performance, but table variables provide the added advantage of automatic memory management. Recursive CTEs should be reserved for simpler, shallower hierarchies due to their performance cost.

Suggested Further Exploration

1. Expand to millions of rows and observe how tempdb scales.
2. Simulate memory pressure with DBCC DROPCLEANBUFFERS.
3. Compare execution on SQL Server 2016, 2019, and 2022.
4. Add indexing strategies and track their impact.
5. Visualize tempdb usage in real time using sys.dm_db_task_space_usage.

Summary

These analysis help uncover optimisations that impact tempdb performance during recursion.

For large-scale or production environments, it’s essential to test both internal and user object allocations under real workloads.  Only through such workload-specific benchmarking can DBAs and developers truly optimize recursive queries for performance and scalability.

Reference:

1. Microsoft Docs – Table Variables in SQL Server
2. https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191221(v=sql.105)?redirectedfrom=MSDN