Interleaved execution changes the unidirectional pipeline between the optimization and execution phases for a single-query execution and allows plans to adapt during the optimization. This approach solves cardinality estimation issues by executing the applicable subtree and capturing accurate cardinality estimates. The optimization process is then resumed for downstream operations.

If a query references an MSTVF, especially if the query is complex, a suboptimal execution plan is not rare. The reason for this is that the estimated output from an MSTVF is fixed to 100 (prior to SQL Server 2014, it was fixed to 1). This can lead to the wrong choice of join operator, under- or overestimated memory grants, and thus result in poorly performing queries.

The new SQL Server 2017 feature allows Query Optimizer to adjust the execution plan by executing a part of the query with the MSTVF call in order to get the exact number of outputted rows. This leads to a much better plan, which is created and adjusted while the query is executing. That means during the plan generation, SQL Server executes a part of the code to improve the execution plan. This optimization is called interleaved execution.

To demonstrate the change, you will create a simple MSTVF in the WideWorldImporters database that (intentionally) returns order IDs of all orders from the Sales.Orders table:

USE WideWorldImporters;
GO
CREATE OR ALTER FUNCTION dbo.SignificantOrders()
RETURNS @T TABLE
(ID INT NOT NULL)
AS
BEGIN
    INSERT INTO @T
    SELECT OrderId FROM Sales.Orders
    RETURN
END
GO

Now, you will use this function in a sample statement in the same database under the compatibility levels 130 and 140 respectively. Run this code to simulate the execution under SQL Server 2016 and observe the execution plan:

ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 130;
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT ol.OrderID,  ol.UnitPrice, ol.StockItemID 
FROM Sales.Orderlines ol
INNER JOIN dbo.SignificantOrders() f1 ON f1.Id = ol.OrderID
WHERE PackageTypeID = 7;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

This query has the execution plan shown in the following screenshot:

You can see the Query Optimizer decided to use Nested Loop Operator, since only 100 outputted rows are expected. You can also see that the Actual Number of Rows is 73,595 and, due to this fixed estimation, the execution plan is not optimal. Here is the output that you get when you set the STATISTICS IO ON and STATISTTCS TIME ON options:

Table 'OrderLines'. Scan count 73595, logical reads 866031, physical reads 47,... 
Table '#AFCC5499'. Scan count 1, logical reads 119, physical reads 0, read-ahead...
SQL Server Execution Times:
   CPU time = 1093 ms,  elapsed time = 2189 ms.

Run this code in the latest compatibility level (140) in SQL Server 2017 and observe the execution plan:

ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 140;
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT ol.OrderID,  ol.UnitPrice, ol.StockItemID 
FROM Sales.Orderlines ol
INNER JOIN dbo.SignificantOrders() f1 ON f1.Id = ol.OrderID
WHERE PackageTypeID = 7;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

The execution plan is shown in the following screenshot:

The plan with the Hash Match Join operator is more appropriate for this cardinality. The execution parameters look better:

Table 'OrderLines'. Scan count 4, logical reads 391, physical reads 0, read-ahead reads 0, lob logical reads 163...
Table 'OrderLines'. Segment reads 1, segment skipped 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0...
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0...
Table '#B2A8C144'. Scan count 1, logical reads 119, physical reads 0, read-ahead reads 0...

 SQL Server Execution Times:
   CPU time = 406 ms,  elapsed time = 1458 ms.

As you can see, CPU time is reduced by more than 50%, and the query executed 33% faster. The improvement is not spectacular, but this is a simple query. In complex queries, interleaved execution can bring significant performance improvements.

Interleaved execution will help with workload performance issues that are due to these fixed cardinality estimates associated with multi-statement table-valued functions.