AzureFeeds

Introduction

Memory-optimized table variables (MOTVs) have been available since SQL Server 2014, but many workloads still use regular on-disk table variables. The common assumption is that MOTVs are “a bit faster” – but how much faster, and under what conditions? When does the difference justify the change?

To answer this, we ran a set of benchmarks comparing MOTVs against on-disk table variables across the dimensions that matter in production: caching scenarios, concurrency levels, row counts, and platforms. The results were surprising in several ways – sometimes MOTVs were barely faster, and in one scenario on-disk variables were actually quicker. But under conditions commonly seen in production, the difference was dramatic.

Throughout this post, we use the shorthand MOTV for memory-optimized table variables and DiskTV for regular on-disk table variables. Both are typed table variables created with CREATE TYPE … AS TABLE; the only difference is that MOTVs include WITH (MEMORY_OPTIMIZED = ON) in the type definition. The query code that uses them – INSERT, SELECT, JOINs – stays the same. One practical difference is that MOTVs must use an explicitly created table type (DECLARE tv MyTableType), whereas DiskTVs can also be declared inline (DECLARE tv TABLE (col1 int, …)). If your existing code uses inline declarations, switching to MOTVs requires defining a named type first.

MOTVs are available in SQL Server (2014 and later), Azure SQL Managed Instance, and Azure SQL Database. For brevity, we use Microsoft SQL throughout this post to refer to all three platforms collectively.

Note: Results will vary depending on hardware, Microsoft SQL version, database configuration, and workload patterns. We recommend validating performance in your own dev/test environment. All tests were run on SQL Server 2025 (8 logical processors) and Azure SQL Database Hyperscale (4 vCores).

---

Background: Temporary Data Options in Microsoft SQL

Microsoft SQL offers several ways to handle temporary data, each with different trade-offs:

• Temporary tables are on-disk tables in tempdb. They can suffer from metadata latch contention and compilation overhead, though object caching and ongoing tempdb improvements have helped significantly.


• Table variables were added in SQL Server 2000 to reduce compilation volumes compared to temp tables. SQL Server 2019 added deferred compilation for better cardinality estimates.


• Memory-optimized table variables were introduced with the In-Memory OLTP engine (code-named Hekaton) in SQL Server 2014. They use in-memory structures that bypass tempdb entirely. See Memory Optimization for Faster Temp Table and Table Variables.


• Schema-only in-memory OLTP tables are an alternative for scenarios like high-speed data ingestion, though long-running transactions can block garbage collection.


• Memory-optimized tempdb metadata addresses tempdb system table contention, but has its own risks with long-running transactions.


• Do without – many temporary objects exist because a query was originally broken into multiple statements that pass intermediate results through temp tables or table variables. This was often a performance workaround for older optimizer limitations. With intelligent query processing expanding steadily – adaptive joins, batch mode on rowstore, optimized plan forcing, and others – it is worth revisiting whether the multi-statement pattern is still necessary. A single well-written query that lets the optimizer work end-to-end can eliminate the temp object entirely, removing both the tempdb overhead and the maintenance burden of the staging logic.

This post focuses specifically on the comparison between MOTVs and DiskTVs.

---

Performance: The Full Picture

The headline from existing articles – “MOTVs are faster” – is correct but incomplete. The actual advantage depends heavily on three factors: whether tempdb object caching is active, how many concurrent sessions are running, and whether the workload is executing in a stored procedure or as ad-hoc SQL.

The Three Caching Scenarios

The most important variable is whether the Microsoft SQL engine can cache the tempdb object for DiskTVs between executions. There are three distinct scenarios:

(50 rows, single session, local SQL Server 2025)

In each scenario, the workload is the same: declare a typed table variable, INSERT 50 rows, and run an aggregate SELECT query. What differs is how Microsoft SQL handles the lifecycle of the underlying tempdb object. In the Stored Procedure scenario, the procedure is called via EXEC and the engine caches the tempdb object between calls – on re-entry it only truncates the existing allocation rather than creating and destroying it. In the WHILE Loop scenario, a single batch declares the variable before the loop; each iteration inserts, queries, and deletes rows, but the variable itself persists across iterations so neither type incurs creation overhead. In the Fresh Batch scenario, each iteration passes the SQL text to EXEC(), forcing a fresh compile and a new tempdb object every time – this is the pattern produced by ORM frameworks, application-generated SQL, and dynamic SQL.

In stored procedures, tempdb object caching masks most of the DiskTV overhead, and MOTVs are only 1.4x faster. But a significant share of production table variable usage does not go through stored procedures with cached plans. ORM frameworks, application-generated SQL, and dynamic SQL all hit the fresh batch path where DiskTVs cost 20ms per iteration and MOTVs cost 2ms – nearly a 9x difference.

Concurrency Makes It Worse for DiskTVs

Under concurrent sessions, DiskTV performance degrades sharply because multiple sessions compete for tempdb allocation page latches. MOTVs allocate from per-core partitioned memory pools with no shared latches, so their performance stays relatively stable.

The three-phase breakdown below separates each iteration into DECLARE (object creation), INSERT (populating 50 rows), and SELECT (query). All tests use stored procedure execution (cached):

Three things stand out. First, the DECLARE phase is trivially fast for both types at all concurrency levels – object creation is not the bottleneck. Second, the INSERT phase accounts for 85-91% of total time across every combination. Third, the MOTV INSERT advantage grows steadily with concurrency: from 1.1x at 1 session to 1.9x at 25 sessions, as tempdb latch contention compounds.

With smaller row counts (5 rows instead of 50), the per-iteration timings drop proportionally but the pattern stays the same – the INSERT phase remains the dominant cost and the MOTV advantage at single session is similar (~1.1x). The difference becomes more pronounced under concurrency, as covered below.

The Worst Case: Uncached + Concurrent

Combining uncached (fresh batch) execution with concurrent sessions produces the most realistic worst case for typical production workloads:

(50 rows, local, µs per iteration)

At 25 concurrent sessions with uncached execution, DiskTV takes 68.8ms per iteration while MOTV takes 1.2ms – a 57x difference. MOTV barely notices concurrency in uncached mode because it never touches tempdb regardless of caching state.

Tail Latency

Average numbers mask the production impact. The maximum latency per iteration shows the real risk:

A single DiskTV iteration taking 10 seconds in a 50-session workload is a catastrophic tail event for any latency-sensitive application.

Small Row Counts (5 Rows)

With fewer rows, the INSERT phase shrinks – making the tempdb object creation overhead dominate even more in the uncached case:

Uncached 5-row DiskTV at 10 sessions (36ms) is actually worse than 50-row (9ms). Each session cycles through create/destroy faster with less INSERT work to do, which means the sessions hammer tempdb metadata contention at a higher rate.

Hyperscale Results

On a 4-vCore Azure SQL Hyperscale instance, single-session timings using the WHILE loop scenario confirmed that MOTV creation rates of 86K bare-declares per second are achievable. The MOTV populate advantage is also present on Hyperscale, though the uncached (fresh batch) scenario showed narrower differences because compilation overhead – which is the same for both types – dominates on Hyperscale where tempdb is local to the compute node and therefore faster than on a local developer instance.

---

Maximum Creation Rate

In stored procedure context with tempdb object caching active, DiskTV bare-declare rates actually exceeded MOTV rates (34K/sec vs 22K/sec) because the cached truncate is cheaper than MOTV’s pool allocation and index creation. With single-row INSERTs, MOTVs took the lead at 8.4K/sec vs 5.0K/sec. In the uncached case, MOTVs were 27x faster for 1-row creates (7.9K/sec vs 294/sec), since DiskTVs pay the full tempdb creation penalty on every call.

The key finding is that raw creation rate is not the main differentiator – it is the data path (INSERT/query) under concurrency and without caching where MOTVs deliver transformative improvement.

---

Memory Usage

One common concern about MOTVs is their memory consumption. However, DiskTVs also use memory – they live in the buffer pool. The real question is the difference.

At small row counts (10 rows), the difference is just 24 KB – entirely irrelevant. At 10,000 rows with a hash index, MOTVs actually use less memory than DiskTVs (2,368 KB vs 5,528 KB). For the target use case of high-frequency, small tables, 1,000 concurrent sessions with 10-row MOTVs would use approximately 64 MB of memory – a modest amount on any modern server.

Hash indexes are significantly more memory-efficient than nonclustered indexes for larger row counts. The bucket array itself is small (8 bytes per bucket), so changing the bucket count has limited impact on total memory consumption. However, bucket count has a critical impact on performance: an undersized count causes long hash chains that severely degrade INSERT and lookup speed. Follow the sizing guidance – set the bucket count to 1-2x the expected number of distinct key values.

---

When to Use MOTVs

Following Paul White’s guidance that table variables work best “when the data is small and when plan selection does not depend on the values present”, the scenarios where MOTVs help most are:

• Very high frequency execution. Stored procedures that execute thousands of times per second, each using a small table variable, benefit most from MOTV’s faster population path.


• Concurrent sessions sharing tempdb. MOTVs are decoupled from tempdb, so they do not contend on shared PFS/GAM allocation latches that slow DiskTVs under concurrency.


• Resilience to tempdb stalls. A tempdb autogrow event pauses all tempdb operations. On an 80-vCore Azure SQL Database running 2,000 batch requests per second with table variables, that can exhaust the worker thread pool in seconds. MOTVs are unaffected.


• Data ingestion buffers. Using MOTVs as table-valued parameter (TVP) buffers avoids the tempdb log throughput that on-disk TVPs generate, as described in this IoT ingestion case study.

Scenarios where MOTVs are not a good fit:

• Plan quality depends on data shape. If the optimizer needs accurate cardinality estimates – large row counts, complex joins requiring column histograms – table variables (regular or memory-optimized) are the wrong tool. Use temp tables instead.


• Low-frequency, large data loads. MOTVs do not support parallel INSERT, so bulk loading large volumes into a MOTV will be single-threaded. At high execution frequency, going parallel can cause more problems than it solves, but for infrequent large loads, DiskTVs or temp tables are a better choice.


• DML on the table variable after population. MOTVs use the In-Memory OLTP versioning engine, which retains every version of every row. Garbage collection cannot reclaim these versions while the variable is in scope, so if code loads data into a MOTV and then updates rows – for example, calculating a scoring field – each UPDATE creates a new row version that persists until the variable is destroyed. Memory consumption can double or more depending on the number of update passes. This is a significant anti-pattern for MOTVs.


• Long-running transactions. For the same reason, a MOTV used inside a long-running transaction accumulates row versions that cannot be reclaimed by garbage collection until the variable goes out of scope. A common example is a single-row “next ID” table that is updated on every call – while each individual update is fast, every version is retained, and memory will grow continuously.

---

When Does It Not Matter?

In cached mode (stored procedure) with 10 or fewer concurrent sessions, the MOTV advantage is 1.2-1.3x – meaningful but not dramatic. If your workload uses stored procedures exclusively and runs at low concurrency, the benefit of switching to MOTVs is modest.

The case for MOTVs is strongest when any of these conditions apply:

• Execution is uncached – ad-hoc queries, ORM-generated SQL, or dynamic SQL


• Concurrency is moderate to high – 10 or more concurrent sessions using table variables


• Table variables are used at high frequency – hundreds or thousands of times per second


• Tail latency matters – even occasional 10x spikes in DiskTV latency are unacceptable

---

Conclusion

MOTVs deliver a modest but real improvement in stored procedure workloads at low concurrency. Under conditions commonly seen in production – uncached execution, moderate concurrency, and high frequency – the improvement becomes dramatic, reaching up to 57x faster in our tests. The change requires no application code modification beyond the table type definition, and the memory overhead is manageable for the small-table workloads where MOTVs are most beneficial.

---

Test Scripts

The scripts used for all benchmarks in this post are included below. Each can be run independently on any Microsoft SQL instance (SQL Server 2019+, Azure SQL MI, or Azure SQL Database) with a database configured with a MEMORY_OPTIMIZED_DATA filegroup.

Setup: Table Types

— Regular (DiskTV) table type
CREATE TYPE dbo.BlogTest_Regular AS TABLE
(
ID int NOT NULL PRIMARY KEY,
ProductName nvarchar(50) NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(10,2) NOT NULL,
OrderDate datetime2(0) NOT NULL
);
GO

— Memory-optimized (MOTV) table type
CREATE TYPE dbo.BlogTest_MemOpt AS TABLE
(
ID int NOT NULL PRIMARY KEY NONCLUSTERED,
ProductName nvarchar(50) NOT NULL,
Quantity int NOT NULL,
UnitPrice decimal(10,2) NOT NULL,
OrderDate datetime2(0) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON);
GO

Test: Three-Phase Breakdown (Create/Populate/Query)

— 3-phase breakdown: single session, 1000 iterations
— Measures DECLARE, INSERT, and SELECT phases independently
DECLARE @Iterations int = 1000, @Warmup int = 100;
DECLARE RAW TABLE (TestName nvarchar(60), i int, CreateUS bigint,
PopulateUS bigint, QueryUS bigint, TotalUS bigint);
DECLARE @i int, T0 datetime2(7), @t1 datetime2(7),
t2 datetime2(7), T3 datetime2(7), @sink decimal(18,2);

— Warmup
SET @i=0;
WHILE @i 20;
DELETE FROM @w;
SET @i += 1;
END;

— DiskTV measured run
SET @i = 0;
WHILE @i < @Iterations BEGIN
SET T0 = SYSDATETIME();
DECLARE @r dbo.BlogTest_Regular;

SET @t1 = SYSDATETIME();
INSERT INTO @r (ID,ProductName,Quantity,UnitPrice,OrderDate)
SELECT TOP(50) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)),
CONCAT(‘P-‘, ROW_NUMBER() OVER(ORDER BY(SELECT NULL))),
ABS(CHECKSUM(NEWID())) % 100 + 1,
CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS decimal(10,2)),
DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 365), SYSDATETIME())
FROM sys.all_objects;

SET t2 = SYSDATETIME();
SELECT @sink = SUM(Quantity * UnitPrice) FROM @r WHERE Quantity > 20;

SET T3 = SYSDATETIME();
DELETE FROM @r;

INSERT INTO RAW VALUES(‘DiskTV’, @i,
DATEDIFF_BIG(MICROSECOND, T0, @t1),
DATEDIFF_BIG(MICROSECOND, @t1, t2),
DATEDIFF_BIG(MICROSECOND, t2, T3),
DATEDIFF_BIG(MICROSECOND, T0, T3));
SET @i += 1;
END;

— Repeat warmup + measured run for MOTV (replace BlogTest_Regular with BlogTest_MemOpt)
— … (same pattern, change type name)

— Results
SELECT TestName,
AVG(CreateUS) AS Avg_Create_US,
AVG(PopulateUS) AS Avg_Populate_US,
AVG(QueryUS) AS Avg_Query_US,
AVG(TotalUS) AS Avg_Total_US
FROM RAW
GROUP BY TestName
ORDER BY TestName;

Test: Caching Scenarios (Cached vs Uncached)

— Scenario A: Stored Procedure (cached)
CREATE PROCEDURE dbo.usp_Test_DiskTV AS BEGIN
SET NOCOUNT ON;
DECLARE tv dbo.BlogTest_Regular;
INSERT INTO tv (ID,ProductName,Quantity,UnitPrice,OrderDate)
SELECT TOP(50) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)),
CONCAT(‘P-‘, ROW_NUMBER() OVER(ORDER BY(SELECT NULL))),
ABS(CHECKSUM(NEWID())) % 100 + 1,
CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS decimal(10,2)),
DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 365), SYSDATETIME())
FROM sys.all_objects;
DECLARE @sink decimal(18,2);
SELECT @sink = SUM(Quantity * UnitPrice) FROM tv WHERE Quantity > 20;
END;
GO

— Scenario A (MOTV variant): same procedure using the memory-optimized type
CREATE PROCEDURE dbo.usp_Test_MOTV AS BEGIN
SET NOCOUNT ON;
DECLARE tv dbo.BlogTest_MemOpt;
INSERT INTO tv (ID,ProductName,Quantity,UnitPrice,OrderDate)
SELECT TOP(50) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)),
CONCAT(‘P-‘, ROW_NUMBER() OVER(ORDER BY(SELECT NULL))),
ABS(CHECKSUM(NEWID())) % 100 + 1,
CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS decimal(10,2)),
DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 365), SYSDATETIME())
FROM sys.all_objects;
DECLARE @sink decimal(18,2);
SELECT @sink = SUM(Quantity * UnitPrice) FROM tv WHERE Quantity > 20;
END;
GO

— Scenario C: Fresh batch (uncached) – run each iteration via EXEC()
DECLARE @sql nvarchar(max) = N’
DECLARE tv dbo.BlogTest_Regular;
INSERT INTO tv (ID,ProductName,Quantity,UnitPrice,OrderDate)
SELECT TOP(50) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)),
CONCAT(”P-”, ROW_NUMBER() OVER(ORDER BY(SELECT NULL))),
ABS(CHECKSUM(NEWID())) % 100 + 1,
CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS decimal(10,2)),
DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 365), SYSDATETIME())
FROM sys.all_objects;
DECLARE @sink decimal(18,2);
SELECT @sink = SUM(Quantity*UnitPrice) FROM tv WHERE Quantity > 20;’;

DECLARE @i int = 0, T0 datetime2(7), @t1 datetime2(7);
SET T0 = SYSDATETIME();
WHILE @i < 1000 BEGIN EXEC(@sql); SET @i += 1; END;
SET @t1 = SYSDATETIME();
SELECT DATEDIFF_BIG(MICROSECOND, T0, @t1) / 1000 AS Avg_US_Per_Iteration;

Test: Concurrent Sessions (PowerShell driver)

# Run N concurrent sessions, each executing a stored procedure
$server = “YOUR-SERVER-INSTANCE” # e.g. “localhostsql25” or “myserver.database.windows.net”
$db = “YOUR-DATABASE” # database with MEMORY_OPTIMIZED_DATA filegroup

foreach ($level in @(1, 10, 25)) {
foreach ($type in @(‘DiskTV’,’MOTV’)) {
$proc = “dbo.usp_Test_$type”
Write-Host “— $type at $level sessions —”
$jobs = @()
for ($s = 0; $s -lt $level; $s++) {
$sql = “EXEC $proc @Iterations=500, @Warmup=50;”
$jobs += Start-Job -ScriptBlock {
param($svr, $database, $query)
sqlcmd -S $svr -d $database -Q $query -t 600
} -ArgumentList $server, $db, $sql
}
$jobs | Wait-Job | Out-Null
$jobs | Remove-Job -Force
Write-Host ” Done.”
}
}

Test: Memory Usage Comparison

— Helper function: current MEMORYCLERK_XTP total (KB)
CREATE OR ALTER FUNCTION dbo.fn_XTP_MemoryKB()
RETURNS bigint
AS
BEGIN
DECLARE KB bigint;
SELECT KB = SUM(pages_kb + virtual_memory_committed_kb
+ awe_allocated_kb + shared_memory_committed_kb)
FROM sys.dm_os_memory_clerks
WHERE type = ‘MEMORYCLERK_XTP’;
RETURN ISNULL(@kb, 0);
END;
GO

— Measure MOTV memory: capture XTP clerk before and after populating
DECLARE Baseline bigint = dbo.fn_XTP_MemoryKB();

DECLARE tv dbo.BlogTest_MemOpt;
INSERT INTO tv (ID, ProductName, Quantity, UnitPrice, OrderDate)
SELECT TOP(50) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)),
CONCAT(‘P-‘, ROW_NUMBER() OVER(ORDER BY(SELECT NULL))),
ABS(CHECKSUM(NEWID())) % 100 + 1,
CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS decimal(10,2)),
DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 365), SYSDATETIME())
FROM sys.all_objects;

DECLARE @after bigint = dbo.fn_XTP_MemoryKB();
SELECT @after – Baseline AS MOTV_Memory_KB;
GO

— Measure DiskTV memory: count buffer pool pages in tempdb
— Run this in a stored proc so the TV is alive during measurement
CREATE OR ALTER PROCEDURE dbo.usp_MeasureDiskTV @RowCount int
AS BEGIN
SET NOCOUNT ON;
DECLARE tv dbo.BlogTest_Regular;

INSERT INTO tv (ID, ProductName, Quantity, UnitPrice, OrderDate)
SELECT TOP(@RowCount) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)),
CONCAT(‘P-‘, ROW_NUMBER() OVER(ORDER BY(SELECT NULL))),
ABS(CHECKSUM(NEWID())) % 100 + 1,
CAST(ABS(CHECKSUM(NEWID())) % 10000 / 100.0 AS decimal(10,2)),
DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 365), SYSDATETIME())
FROM sys.all_objects a CROSS JOIN sys.all_objects b;

— Count tempdb pages allocated to this session’s internal objects
SELECT (internal_objects_alloc_page_count
– internal_objects_dealloc_page_count) * 8 AS DiskTV_Memory_KB
FROM sys.dm_db_task_space_usage
WHERE session_id = @@SPID;
END;
GO
EXEC dbo.usp_MeasureDiskTV @RowCount = 50;

Note: DiskTV memory measurement via dm_db_task_space_usage can return 0 for small row counts when pages are cached. For DiskTV, the buffer pool query from dm_os_buffer_descriptors filtered to tempdb is the most reliable method, but requires a second session to query while the first holds the TV alive. The MOTV measurement via MEMORYCLERK_XTP differencing works reliably on a quiet instance.

---

Feedback and Suggestions

If you have feedback or suggestions, please contact the Databases SQL Customer Success Engineering (Ninja) Team (datasqlninja@microsoft.com).

Note: For additional information about migrating various source databases to Azure, see the Azure Database Migration Guide.