When Microsoft introduced Table Variable Deferred Compilation (TVDC) in SQL Server 2019, table variables finally became capable of generating plans based on actual cardinalities instead of the notorious one-row estimate. If TVDC causes problems, Microsoft provide the query hint:
USE HINT('DISABLE_DEFERRED_COMPILATION_TV')
At first glance the hint appears straightforward. If TVDC introduces regressions in your workload, you can disable the feature, allowing SQL Server to revert to the legacy table variable behavior while preserving previous plan characteristics. However, an Extended Events Trace reveals something unexpected. Even when TVDC is explicitly disabled, SQL Server still performs a statement recompilation with the reason Deferred Compilation. Even more surprising, the statement appears to start twice although it executes only once.
The Test Case
/* Declaration of Table Variable */
DECLARE @c_cust_list TABLE (c_custkey BIGINT PRIMARY KEY CLUSTERED);
/* Inserting customers from Austria */
INSERT INTO @c_cust_list(c_custkey)
SELECT c.c_custkey
FROM dbo.customers AS c
INNER JOIN dbo.nations AS n
ON (c.c_nationkey = n.n_nationkey)
WHERE n.n_name = 'AUSTRIA';
/* Running the Query without Deferred Compilation! */
SELECT o.o_custkey AS customer_number,
COUNT_BIG(*) AS customer_orders
FROM demo.orders AS o
INNER JOIN @c_cust_list AS l
ON (o.o_custkey = l.c_custkey)
GROUP BY
o.o_custkey
ORDER BY
customer_orders DESC
OPTION (
USE HINT('DISABLE_DEFERRED_COMPILATION_TV'),
MAXDOP 4
);
A Table Variable with a clustered primary key is populated with all customer IDs from Austria . The second query aggregates the number of orders per customer and returns the customers with the highest order counts first. Although the query hint explicitly disables Table Variable Deferred Compilation while allowing a maximum degree of parallelism of four.
The Execution Plan reveals the classic behavior of a table variable when Table Variable Deferred Compilation is disabled. The highlighted Clustered Index Scan on @c_cust_list estimates only 1 row, while the actual number of rows is 3,776, resulting in an estimation error of 377,600%. Because the optimizer believes that only a single row will be returned, it chooses a Nested Loops Join, which ultimately processes 2,000,000 rows instead of the estimated 410 rows, causing the cardinality errors to propagate through the remainder of the plan.
Non Expected Behavior behind the Scene
I expect the Query Optimizer to recognize that „Deferred Compilation“ should not be applied and to continue query execution after the rows have been loaded into the table variable. However, captured Extended Event Traces show a different picture (event_id 4 – 10).
The Extended Events Trace shows that SQL Server initially recognizes the table variable as a candidate for Table Variable Deferred Compilation (event_id = 1), but immediately disables the feature because of the DISABLE_DEFERRED_COMPILATION_TV query hint (event_id = 2).
Despite the feature being disabled, the INSERT statement still triggers a statement-level recompilation with the reason Deferred Compilation (event_id = 5), causing the statement to start twice. This demonstrates that disabling TVDC does not prevent SQL Server from entering the Deferred Compilation Framework, resulting in an additional recompile before the statement is actually executed.
Does the Statement really start twice?
The answer is „NO“:
- The INSERT never actually executed.
- SQL Server enters the statement.
- Then something triggers the deferred-compilation mechanism.
- The current execution attempt is abandoned.
- A new plan is generated (which took 4.530 ms!).
- Now the same statement starts again (event_id = 7).
This is the event that initially looks suspicious, but from SQL Server’s perspective this is actually the first real execution. The previous attempt reached only the recompilation checkpoint.
Why is „Deferred Compilation“ requested after TVDC was already disabled during compilation?
The Extended Events Trace suggests that the query hint only suppresses the benefit of Table Variable Deferred Compilation rather than the deferred compilation process itself. SQL Server still enters the deferred compilation framework and triggers a statement-level recompile with the reason Deferred Compilation, even though TVDC has been disabled. This behavior explains why the INSERT statement appears twice in the trace and why a recompilation occurs before the statement is actually executed.
Side notes on this behavior
Q: Does the Recompilation Phase have an impact on performance?
Usually only during the initial compilation of a new execution plan. The Extended Events Trace shows that SQL Server performs a statement-level recompilation with the reason Deferred Compilation before the plan is cached, but subsequent executions can reuse the cached plan and therefore avoid this additional recompilation step. However, workloads that use dynamic code and continuously generate new query texts may trigger new compilations and the associated deferred-compilation recompile repeatedly, increasing CPU overhead and plan cache usage.
Extended Events show repeated Deferred compile recompilations for a query using a table variable. Even when the query is executed with the DISABLE_DEFERRED_COMPILATION_TV hint, SQL Server may still trigger deferred recompiles to obtain accurate row counts for the table variable. The hint disables Table Variable Deferred Compilation during optimization, but it does not prevent recompilations related to table variable cardinality discovery.
Q: Why does the statement appear twice in the Extended Events trace?
The first sql_statement_starting event belongs to the initial execution attempt that is interrupted by the deferred compilation recompile. After recompilation completes, SQL Server restarts the statement, resulting in a second sql_statement_starting event before the statement finally executes.
Q: Does the statement actually execute twice?
No. The trace shows two statement starts, but only a single completed execution. The first execution attempt is abandoned before execution begins because SQL Server enters the deferred compilation phase.
Q: Can this behavior be observed inside stored procedures?
Yes. Stored Procedures are the easiest place to reproduce the behavior. Table Variables are commonly used there and the plan cache behavior can be observed consistently.
Q: Does this behavior affect cardinality estimates?
Yes. Because TVDC is disabled, SQL Server falls back to legacy table variable assumptions and uses traditional row estimates. The table variable was estimated at a single row while actually containing 3,776 rows, resulting in severe cardinality estimation errors.
Q: Is this behavior documented by Microsoft?
The documentation explains the purpose of DISABLE_DEFERRED_COMPILATION_TV. It does not explicitly state that SQL Server may still perform a statement-level recompile with the reason Deferred Compilation. The Extended Events trace therefore exposes an interesting implementation detail that is not immediately obvious from the documentation.
Q: Is this behavior a bug?
Not necessarily. The trace suggests that SQL Server differentiates between using TVDC and entering the deferred compilation framework. The observed behavior appears to be by design, although the implementation detail is somewhat unexpected.
Thank you for Reading!
