Query hints are often treated as a shortcut to solve performance problems. A query doesn’t get the desired plan? Add a hint. Cardinality estimates look suspicious? Add another hint. Parameter sensitivity issues? Surely there’s a hint for that too. Over the years I’ve learned that query hints can be useful, but they can also come with side effects that aren’t immediately visible. The relationship between OPTIMIZE FOR UNKNOWN and Interleaved Execution is a perfect example of how a single hint can silently disable optimizer features that were already doing a surprisingly good job.
While testing Multi-Statement Table-Valued Functions (MSTVFs), I ran into exactly such a scenario. The function benefited from Interleaved Execution and produced accurate cardinality estimates.
Understanding the Test Setup
To demonstrate the behavior, I will use a very simple Multi-Statement Table-Valued Function (MSTVF). The Function returns a list of customer keys for a given nation and stores the result in a table variable before returning it to the calling query.
CREATE OR ALTER FUNCTION demo.get_c_custkey_list_by_nation (@n_name CHAR(25))
RETURNS @customers TABLE
(c_custkey BIGINT NOT NULL PRIMARY KEY CLUSTERED)
AS
BEGIN
INSERT INTO @customers (c_custkey)
SELECT c_custkey
FROM dbo.customers AS c
INNER JOIN dbo.nations AS n
ON (c.c_nationkey = n.n_nationkey)
WHERE n.n_name = @n_name;
RETURN;
END
GO
Interleaved Execution in Action
To demonstrate the behavior, let’s use the MSTVF in a more realistic reporting procedure. The Stored Procedure retrieves all customers from a specific nation, calculates their most recent order date, and counts the total number of orders per customer.
CREATE OR ALTER PROCEDURE dbo.get_customer_info_by_country
@n_name CHAR(25)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
SELECT c.c_custkey,
c.c_name,
MAX(o.o_orderdate) AS last_order_date,
COUNT_BIG(o.o_orderkey) AS max_orders
FROM dbo.customers AS c
INNER JOIN dbo.orders AS o
ON (c.c_custkey = o.o_custkey)
INNER JOIN demo.get_c_custkey_list_by_nation(@n_name) AS ci
ON (c.c_custkey = ci.c_custkey)
GROUP BY
c.c_custkey,
c.c_name
ORDER BY
last_order_date,
c.c_name;
END
GO
/* Execution of the stored procedure for GERMANY -> ~40k rows */
EXEC dbo.dbo.get_customer_info_by_country @n_name = 'GERMANY';
GO
In my test environment, the MSTVF returns approximately 40,000 customer keys for the selected nation. This makes the Function an excellent candidate for Interleaved Execution because the number of rows returned by the MSTVF has a significant impact on the overall query plan.
The actual execution plan clearly shows that Interleaved Execution was used. The MSTVF is executed during compilation and delivers approximately 40,000 rows to the optimizer before the final plan is produced.
- The cardinality estimate closely matches reality.
- Join algorithms are chosen based on the actual row count.
- Memory grants are sized appropriately.
- Aggregate operators can be optimized using realistic estimates.
Extended Events allow us to observe the process directly. To better understand what SQL Server is doing internally, I captured the execution using the Interleaved Execution phases in an Extended Event. Before the query is fully optimized, SQL Server triggers a recompilation event named recompilation_for_interleaved_exec. This indicates that the optimizer has identified an MSTVF that qualifies for Interleaved Execution and that the optimization process is about to enter the Interleaved Execution framework.
interleaved_exec_status = 0 means, that SQL Server has recognized that the query contains an MSTVF that is a candidate for Interleaved Execution. The optimizer pauses normal optimization and prepares to execute the function in isolation.
interleaved_exec_status = 1 means, that SQL Server executes the MSTVF during compilation and captures the actual number of rows returned by the function. After the execution the stats will be updated (interleaved_exec_stats_update).
interleaved_exec_status = 2 means, that interleaved execution is finished and the optimizer resumes compilation and incorporates the observed cardinality into the remainder of the optimization process.
Why Interleaved Execution Is So Valuable Here?
SQL Server does not have to rely entirely on assumptions. During optimization, the optimizer enters the Interleaved Execution framework, executes the MSTVF, and observes the actual number of rows returned by the function. This is exactly why Interleaved Execution is such a powerful feature.
What Happens When Yesterday’s Tuning Technique Meets Today’s Optimizer?
Imagine a developer who spent years working with SQL Server before 2017. One of the most critical reporting queries in the application uses a Multi-Statement Table-Valued Function, and from time to time the query experiences parameter sensitivity issues. Different parameter values produce different plans, and some executions perform significantly worse than others. The developer decides to use OPTIMIZE FOR UNKNOWN as a Query Hint. The hint works exactly as intended. SQL Server stops optimizing for specific parameter values and generates a more generic plan. The query becomes stable and the issue is considered solved.
What the developer doesn’t realize is that the optimizer has evolved significantly since SQL Server 2016. Features such as Interleaved Execution can now execute the MSTVF during optimization and obtain the actual row count before the final plan is generated. Unfortunately, the optimizer never gets that chance!
CREATE OR ALTER PROCEDURE dbo.get_customer_info_by_country
@n_name CHAR(25)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
SELECT c.c_custkey,
c.c_name,
MAX(o.o_orderdate) AS last_order_date,
COUNT_BIG(o.o_orderkey) AS max_orders
FROM dbo.customers AS c
INNER JOIN dbo.orders AS o
ON (c.c_custkey = o.o_custkey)
INNER JOIN demo.get_c_custkey_list_by_nation(@n_name) AS ci
ON (c.c_custkey = ci.c_custkey)
GROUP BY
c.c_custkey,
c.c_name
ORDER BY
last_order_date,
c.c_name
OPTION (OPTIMIZE FOR UNKNOWN);
END
GO
/* Execution of the stored procedure for GERMANY -> ~40k rows */
EXEC dbo.dbo.get_customer_info_by_country @n_name = 'GERMANY';
GO
After adding OPTION (OPTIMIZE FOR UNKNOWN), SQL Server no longer uses Interleaved Execution for the MSTVF. As a result, the optimizer estimates only 100 rows although the function actually returns 60,128 rows. This massive estimation error leads to a Nested Loops plan with excessive Key Lookups and significantly more work than necessary.
Because Interleaved Execution is disabled, the optimizer no longer knows how many rows the MSTVF will actually return and must rely on a generic estimate of 100 rows. Based on this assumption, SQL Server concludes that the workload is small enough to be processed efficiently by a serial plan.
Interleaved Execution works!
Interleaved Execution disabled!
The statistics clearly show the benefit of Interleaved Execution. Because SQL Server knows the actual cardinality of the MSTVF during optimization, it can choose a plan that accesses the underlying tables efficiently and processes the result set with a minimal number of scans. This allows the optimizer to avoid excessive lookup operations and significantly reduce the amount of I/O required to complete the query. The result is an execution plan that finishes quickly and scales well with the actual workload returned by the function.
The statistics reveal the cost of disabling Interleaved Execution. Because the optimizer no longer knows the actual cardinality returned by the MSTVF, it chooses a plan that repeatedly accesses the orders table instead of processing the data in a more scalable manner. This increases the number of scans and logical reads required to complete the query. As a result, the query spends significantly more time performing I/O and takes considerably longer to finish than the version that benefits from Interleaved Execution.
Understand the Optimizer Before Reaching for a Query Hint
This example demonstrates why query hints should be used sparingly and only after understanding the problem they are intended to solve. What was once a perfectly reasonable tuning technique, can become a performance regression because the optimizer had evolved and gained new adaptive capabilities. By forcing OPTIMIZE FOR UNKNOWN, the query lost the benefits of Interleaved Execution and reverted to a plan based on assumptions instead of actual cardinality information. The lesson is not that query hints are inherently bad, but that every hint comes with trade-offs that may change as SQL Server introduces new optimization features. Before adding a hint, make sure you understand what the optimizer is already doing.
A Final Thought on Query Hints and Modern Optimizer Features
Before we condemn OPTIMIZE FOR UNKNOWN, it is important to understand the purpose of this article. The goal is not to argue that query hints are bad or that older tuning techniques have become obsolete. Instead, this post demonstrates how modern Intelligent Query Processing (IQP) features can interact with tuning strategies that were introduced long before those features existed.
In fact, there are scenarios where OPTIMIZE FOR UNKNOWN can still be the better choice. Consider a stored procedure suffering from parameter sniffing. During the initial compilation, Interleaved Execution allows SQL Server to execute the MSTVF and obtain an accurate row count. However, that row count becomes part of the cached execution plan and reflects only the parameter values used during the first execution. If subsequent executions return significantly different cardinalities, the cached plan may no longer be an appropriate choice and performance can degrade.
In such situations, sacrificing Interleaved Execution in favor of a more generic and predictable plan may be a reasonable trade-off. The key takeaway is that neither IQP features nor query hints should be viewed in isolation. Understanding the workload, the data distribution, and the optimizer’s behavior is far more important than following a tuning pattern simply because it worked well in a previous version of SQL Server.
Thank you for reading!
