Memory-Optimized TempDB and RESOURCE_SEMAPHORE Waits

The Question: Memory-Optimized TempDB?

Developers often ask: "Can I make TempDB memory-optimized to speed it up?"

Short answer: No. TempDB itself cannot be memory-optimized.

But that's not the real issue. The real issue is usually RESOURCE_SEMAPHORE waits — queries waiting for memory grants. Understanding this distinction is key to solving the underlying problem.

Why You Can't Memory-Optimize TempDB

TempDB is Special

TempDB serves all users and all workloads. It's system-wide, not database-specific:

User-defined temp tables (#temp, ##global)
Internal work tables (sorts, hashes, cursors, row versioning)
Service Broker queues
Spills from memory-optimized tables

Memory-optimized tables are designed for specific, controlled use cases within user databases. TempDB's unpredictability makes it unsuitable.

Technical Constraints

SQL Server doesn't allow memory-optimized tables in TempDB because:

Memory-optimized filegroups are database-scoped, not system-scoped
TempDB is recreated on restart; memory-optimized tables would be wiped
TempDB supports features (triggers, foreign keys) that conflict with memory-optimized limitations

Understanding RESOURCE_SEMAPHORE Waits

What is a RESOURCE_SEMAPHORE?

A semaphore is a gatekeeper. RESOURCE_SEMAPHORE gates access to memory grants. When a query needs memory (for sorting, hashing, etc.), it must acquire a grant from this semaphore.

Query arrives
  ↓
Requests memory grant (e.g., 100 MB for sort)
  ↓
Semaphore checks: Is memory available?
  ↓
If yes: Grant immediately, query executes
If no: Wait in queue (RESOURCE_SEMAPHORE wait)
  ↓
When memory frees up, grant the waiting query
  ↓
Query executes

When Do RESOURCE_SEMAPHORE Waits Occur?

When available query memory is exhausted:

Memory-intensive queries running concurrently (sorts, joins, hashes)
Insufficient server memory (max server memory set too low)
Other workloads consuming memory (memory-optimized tables, columnstore, external processes)
Memory-resident TempDB tables (large working sets in temp tables)

Detecting RESOURCE_SEMAPHORE Waits

-- Check current waits
SELECT
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS DECIMAL(5,2)) AS pct_wait
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE')
ORDER BY wait_time_ms DESC;

-- High RESOURCE_SEMAPHORE = Memory pressure
-- Queries are waiting to get memory grants

Query Memory Grants

Every query that needs to sort or hash gets a memory grant:

-- Check what queries are requesting memory
SELECT
    session_id,
    requested_memory_kb,
    granted_memory_kb,
    is_small,
    sql_handle
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC;

-- If requested > granted, query is waiting
-- If many queries waiting, semaphore is saturated

The Real Problem: TempDB Spillage

Why TempDB Fills Up

When queries don't get enough memory to sort/hash, they spill to TempDB:

-- Query needs 500 MB to sort 10 million rows
SELECT TOP 10 * FROM LargeTable ORDER BY SortColumn;

-- Scenario 1: Memory available
-- Get 500 MB grant, sort in memory, execute in 100 ms

-- Scenario 2: Memory unavailable (RESOURCE_SEMAPHORE wait)
-- Wait for memory to free up (could be seconds to minutes)
-- Once memory available, get grant, sort in memory, execute slowly

-- Scenario 3: Memory unavailable, query times out
-- Spill to TempDB: sort partially in memory, rest on disk
-- Execution now depends on TempDB I/O (much slower)

Vicious Cycle

Heavy TempDB usage causes TempDB contention, which you try to fix by... optimizing TempDB. But the real problem is memory pressure.

Mistake: "Let's add more TempDB files"
Reality: More files help if contention is at the allocation page (GAM/SGAM). But if queries are spilling because of memory pressure, more files won't help — queries still need memory.

Solving Memory Pressure

Diagnostic: Is It Memory Pressure?

-- Check both sides:

-- 1. RESOURCE_SEMAPHORE waits high?
SELECT * FROM sys.dm_os_wait_stats
WHERE wait_type = 'RESOURCE_SEMAPHORE';

-- 2. TempDB usage high?
SELECT
    CAST(SUM(user_object_reserved_page_count) * 8 / 1024.0 / 1024.0 AS NUMERIC(10,2)) AS TempDB_Used_GB
FROM sys.dm_db_session_space_usage;

-- 3. Server memory pressure?
SELECT
    (SELECT memory_allocated_for_table_kb / 1024.0 FROM sys.dm_db_xtp_table_memory_stats
     WHERE object_id IS NOT NULL) AS MemOptimized_MB,
    (SELECT cntr_value FROM sys.dm_os_performance_counters
     WHERE counter_name = 'Total Server Memory (KB)') / 1024.0 AS Total_Memory_MB,
    (SELECT cntr_value FROM sys.dm_os_performance_counters
     WHERE counter_name = 'Target Server Memory (KB)') / 1024.0 AS Target_Memory_MB;

Fix 1: Increase max_server_memory

If the server has available RAM, increase SQL Server's memory allocation:

-- Current setting
SELECT * FROM sys.configurations WHERE name = 'max server memory (MB)';

-- Increase (requires restart or may work dynamically)
EXEC sp_configure 'max server memory (MB)', 32000;  -- 32 GB
RECONFIGURE;

-- Don't set too high; leave memory for OS and other processes

Fix 2: Optimize Queries to Reduce Memory Needs

-- Bad: Large sort in memory
SELECT * FROM Orders
ORDER BY OrderDate, CustomerID, Amount
LIMIT 1000000;  -- Sorting 1M rows, potentially gigabytes

-- Better: Add index, reduce data volume
CREATE INDEX idx_order_date ON Orders(OrderDate);
SELECT * FROM Orders
WHERE OrderDate >= '2024-01-01'
ORDER BY OrderDate
LIMIT 1000;  -- Sorting 1K rows, megabytes

Fix 3: Use MAXDOP Hints to Reduce Parallelism

Parallel queries request larger memory grants. Reduce parallelism if memory is tight:

-- This query may request 500 MB for 4-way parallelism
SELECT * FROM LargeTable ORDER BY Column;

-- Reduce memory grant by limiting parallelism
SELECT * FROM LargeTable ORDER BY Column OPTION (MAXDOP 2);

-- Trade: Slightly slower query, but no memory starvation

Fix 4: Batch Processing Instead of Large Transactions

-- Bad: Loads 1M rows into temp table, high memory
SELECT * INTO #TempOrders FROM Orders WHERE Status = 'Pending';
-- Uses 500 MB+ for sort/hash

-- Better: Process in batches
DECLARE @BatchSize INT = 10000;
DECLARE @Offset INT = 0;

WHILE @Offset < (SELECT COUNT(*) FROM Orders WHERE Status = 'Pending')
BEGIN
    SELECT TOP (@BatchSize) * INTO #TempBatch
    FROM Orders
    WHERE Status = 'Pending'
    ORDER BY OrderID
    OFFSET @Offset ROWS;

    -- Process batch
    -- DROP #TempBatch

    SET @Offset = @Offset + @BatchSize;
END;

When Memory-Optimized Tables Help (Indirectly)

Memory-optimized tables can reduce RESOURCE_SEMAPHORE waits indirectly:

Fewer spills: If you move OLTP hot tables to memory-optimized, they consume predictable memory, leaving more for query grants
Lower latency: Fast OLTP operations free memory faster, allowing queued queries to proceed
Less TempDB pressure: Memory-optimized tables don't spill to TempDB (they stay in memory)

But this is indirect. You're not making TempDB memory-optimized; you're reducing overall memory pressure by optimizing workload distribution.

Monitoring and Prevention

Baseline Metrics

-- Collect baseline metrics hourly
CREATE TABLE MemoryBaseline (
    CollectionTime DATETIME2,
    RESOURCE_SEMAPHORE_Waits INT,
    TempDB_Used_GB NUMERIC(10,2),
    Server_Memory_KB BIGINT,
    Target_Memory_KB BIGINT
);

INSERT INTO MemoryBaseline
SELECT
    GETDATE(),
    ISNULL((SELECT waiting_tasks_count FROM sys.dm_os_wait_stats WHERE wait_type = 'RESOURCE_SEMAPHORE'), 0),
    (SELECT CAST(SUM(user_object_reserved_page_count) * 8 / 1024.0 / 1024.0 AS NUMERIC(10,2)) FROM sys.dm_db_session_space_usage),
    (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Total Server Memory (KB)'),
    (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Target Server Memory (KB)');

-- Alert if RESOURCE_SEMAPHORE waits increase significantly

Alerting

Set up alerts for:

RESOURCE_SEMAPHORE wait time > 100 seconds/hour
TempDB usage > 50% of allocated space
Server memory pressure > 80%

Summary: TempDB vs. Memory vs. RESOURCE_SEMAPHORE

Problem	Symptom	Fix
TempDB allocation page contention	PAGELATCH_EX waits on TempDB	Add TempDB files (1 per 4 cores)
Memory pressure (queries spilling)	RESOURCE_SEMAPHORE waits + high TempDB usage	Increase max_server_memory, optimize queries
Insufficient I/O for TempDB	High disk latency on TempDB drive	Move TempDB to faster storage (NVMe)
Too many concurrent large queries	RESOURCE_SEMAPHORE wait spike, all queries slow	Reduce MAXDOP, batch processing, workload management

The Verdict

You can't memory-optimize TempDB directly. But if you're seeing RESOURCE_SEMAPHORE waits, the problem isn't TempDB — it's memory pressure. Fix that by increasing available memory, optimizing queries, or using memory-optimized tables for hot workloads to free up memory for query grants.

The lesson: Understanding the root cause (memory pressure) beats optimizing the symptom (TempDB usage).

← Back to Blog

Memory-Optimized TempDB and RESOURCE_SEMAPHORE Waits: The Connection