When Microsoft introduced Interleaved Execution, many SQL Server professionals wondered whether this new optimization technique could eventually help address Parameter Sniffing as well. Instead of compiling a plan based on a fixed cardinality guess, the optimizer could pause compilation, execute the function, observe the actual number of rows returned, and then resume optimization using real data.
For MSTVFs, this solved one of the most notorious estimation problems in SQL Server. Joins, memory grants, and operator choices suddenly had access to accurate row counts rather than relying on a hardcoded assumption.
At first glance, that immediately raises a much bigger question. If the optimizer is capable of interrupting compilation, executing part of a query, collecting runtime information, and recompiling with actual cardinalities, why doesn’t SQL Server use the same technique to address Parameter Sniffing? After all, Parameter Sniffing is fundamentally another problem caused by making optimization decisions before the true runtime characteristics are known.
However, that’s not how Interleaved Execution works. While it improves cardinality estimation for MSTVFs, it does not eliminate plan sensitivity, nor does it prevent parameter-specific plans from being cached and reused. In some scenarios, the feature can even create a false sense of safety because the row estimate is correct, while the resulting plan is still highly dependent on the parameter value used during compilation.
A Quick Refresher on Parameter Sniffing
Parameter Sniffing occurs when SQL Server compiles a plan using the parameter values of the first execution and then reuses that plan for subsequent executions. This can become problematic when different parameter values return vastly different numbers of rows, making a plan that is optimal for one execution inefficient for another. Historically, Multi-Statement Table-Valued Functions (MSTVFs) suffered from a similar challenge because the optimizer had no reliable cardinality information during compilation. Interleaved Execution addresses that problem by executing the MSTVF during compilation and recompiling the outer query using the actual row count. At first glance, this sounds remarkably similar to what many SQL Server professionals have wished for as a solution to Parameter Sniffing. What if SQL Server could optimize after knowing the actual cardinality?
Interleaved Execution Meets Parameter Sniffing
The relationship between Interleaved Execution and Parameter Sniffing is more interesting than it may appear at first glance. Interleaved Execution is a new estimation features introduced in SQL Server 2017. The optimizer pauses compilation, executes the Multi-Statement Table-Valued Function, captures the actual number of rows, and then recompiles the outer query using that information. At first sight, this sounds remarkably similar to the solution many SQL Server professionals have been asking for in the fight against Parameter Sniffing. In the following demo, we’ll see that while Interleaved Execution improves cardinality estimation for MSTVFs, it does not prevent a plan compiled for one parameter value from being reused for another.
Create the Stored Procedure
/*
Let's first create a stored procedure where we hand over
the name of the country!
*/
CREATE OR ALTER PROCEDURE demo.parameter_sniffing
@n_name CHAR(25) = 'DENMARK'
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
SELECT c.c_custkey,
c.c_name,
cbn.last_orderdate,
cbn.num_of_orders
FROM demo.get_customer_by_nation(@n_name) AS cbn
INNER JOIN dbo.customers AS c
ON (cbn.c_custkey = c.c_custkey)
ORDER BY
cbn.num_of_orders,
c.c_name;
END
GO
This stored procedure is intentionally simple, which makes it ideal for demonstrating the interaction between Interleaved Execution and Parameter Sniffing. The procedure accepts a country name as a parameter and passes that value directly to the Multi-Statement Table-Valued Function demo.get_customer_by_nation. The function returns all customers for the requested country together with aggregated order information, and the result is then joined back to the customers table to retrieve additional attributes. Finally, the result set is sorted by the number of orders and the customer name. Because the cardinality of the MSTVF varies significantly between countries such as DENMARK and AUSTRIA, the procedure provides an excellent test case to investigate whether Interleaved Execution can mitigate the effects of Parameter Sniffing when a plan is compiled for one parameter value and later reused for another.
Compile for DENMARK
/* Let's start with a small country: DENMARK - 10 rows */
EXEC demo.parameter_sniffing @n_name = 'DENMARK';
GO
An Extended Events session provides a view into how Interleaved Execution works behind the scenes. When SQL Server encounters the MSTVF during compilation, it identifies the function as a candidate for Interleaved Execution and triggers a recompilation event. Instead of finishing optimization immediately, compilation is paused and the function is executed to determine its actual cardinality. The resulting row count is fed back into the optimizer, which updates its internal statistics and resumes compilation using the observed number of rows instead of a fixed estimate.
The execution plan demonstrates that Interleaved Execution successfully provided the optimizer with the actual cardinality of the MSTVF result set before the final plan was compiled.
As a result, the optimizer estimates 10 rows for both the MSTVF and all downstream operators, which perfectly matches the 10 actual rows returned during execution. Thanks to these accurate estimates, SQL Server chooses an efficient plan consisting of a Nested Loops join, a sort operation for the final ordering, and a clustered index seek against the customers table.
Compile for AUSTRIA
/* Let's start with a small country: AUSTRIA - 3776 rows */
EXEC demo.parameter_sniffing @n_name = 'DENMARK';
GO
The second execution is where the real story begins. Although the procedure is now executed with AUSTRIA instead of the original parameter value, the Extended Events session no longer shows any of the Interleaved Execution events. There is no recompilation_for_interleaved_exec, no interleaved_exec_status, and no interleaved_exec_stats_update event. Instead, we only see a sql_tvf_build event, indicating that SQL Server is simply rebuilding the internal table variable of the MSTVF during normal execution.
The reason is straightforward: Interleaved Execution is a compilation-time feature, not an execution-time feature. During the first execution, SQL Server paused optimization, executed the MSTVF, captured its actual cardinality, and generated a cached plan based on the parameter value used at compile time. When the procedure is executed again, SQL Server finds a valid plan in the plan cache and reuses it rather than recompiling the statement. Therefore, there is no opportunity to execute the Interleaved Execution workflow again. Since no recompilation occurs, there is no opportunity to execute the Interleaved Execution workflow again.
This behavior demonstrates the key limitation of Interleaved Execution in the context of Parameter Sniffing. While the feature successfully provides accurate cardinality information during the initial compilation, the resulting plan is still cached and reused for subsequent executions. As a consequence, a plan compiled for a small country such as DENMARK may later be reused for a much larger country such as AUSTRIA, even though the number of rows returned by the MSTVF differs significantly. In other words, Interleaved Execution improves cardinality estimation during compilation, but it does not solve the plan reuse problem that lies at the heart of Parameter Sniffing.
The Feature We Wanted vs. The Feature We Got
Interleaved Execution was a step forward for Multi-Statement Table-Valued Functions. For the first time, SQL Server could pause optimization, execute the function, observe its actual cardinality, and continue compilation with information that simply wasn’t available before. The result was an improvement in cardinality estimation and, in many cases, much better execution plans.
Looking at the mechanics behind the feature, it’s hard not to wonder whether the same concept could have been applied to one of the most common challenges in SQL Server: Parameter Sniffing. If the optimizer is already capable of gathering runtime information during compilation, using that information to produce parameter-aware plans seems like a natural next step. However, Interleaved Execution was designed to solve a very specific problem: the lack of cardinality information for MSTVFs. Once the plan is compiled, the familiar rules of plan caching and reuse still apply.
That doesn’t make Interleaved Execution a missed opportunity. Instead, it highlights how difficult the Parameter Sniffing problem really is. Microsoft chose to address MSTVF cardinality estimation with Interleaved Execution and later tackled plan sensitivity with features such as Parameter Sensitive Plan Optimization. Interleaved Execution solved exactly the problem it was designed for, while simultaneously giving us a glimpse of a much larger optimization challenge that SQL Server would continue to address in the years that followed.
Thank you for reading!
