TopAnswers Databases
or
Erik DarlingCC BY-SA 4.0
sql-server sql-server-2017
Interleaved Execution is part of a [family of features][1] in the 2017 query processor that consists of:

 - [Batch Mode Adaptive Joins][2] 
 - Interleaved Execution
 - [Batch Mode Memory Grant Feedback][3]

So how does Interleaved Execution work?


  [1]: https://blogs.technet.microsoft.com/dataplatforminsider/2017/09/28/enhancing-query-performance-with-adaptive-query-processing-in-sql-server-2017/
  [2]: https://topanswers.xyz/databases?q=668
  [3]: https://topanswers.xyz/databases?q=669
Top Answer
CC BY-SA 4.0imported from SE
**The Devil Is In The Table Variable**

Interleaved execution is aimed at correct cardinality misestimated in Multi-Statement Table Valued Functions. 

In prior versions of SQL Server, these functions would always produced rather shoddy estimates:

 - 2014, 2016: 100 rows
 - 2005 - 2012: 1 row

Needless to say, this could cause a lot of problems when joining to other tables. 

While selecting data from a table variable does not inhibit parallelism on its own, the low row estimates would often contribute to low query costs, where parallelism wouldn't be considered. 

With Interleaved Execution, cardinality estimation is paused, the subtree for the MSTVF is executed, and optimzation is resumed with a more accurate cardinality estimate.

**How do I know if my MSTVF receives interleaved execution.**

Like with Adaptive Joins, Interleaved Execution is noted in the query plan. Unlike Adaptive Joins, it is not noted in estimated plans, at least as of this writing.

The plan shape for a MSTVF with Interleaved Execution is a bit different from a typical plan that has a MSTVF in it.

You'll see the Table Valued Function operator at the top of the plan, and the scan of the Table Variable where the TVF operator would normally be in the graphical plan.

[![NUTS][1]][1]

When hovering over the TVF operator, you'll see the attribute `IsInterleavedExecuted` set to True, as well as a an estimated number of rows that may very nearly reflect reality. Hurrah.

**Are there any Extended Events to troubleshoot when Interleaved Execution doesn't occur?**

Yes, a whole bunch:

[![NUTS][2]][2]

Note that some of these are in the Debug channel, which isn't selected by default when searching for Events to Extend.

**Does Interleaved Exection Require ColumnStore Indexes?**

No, they'll work either way. Here's an example:

	SELECT u.Id, mj.*
	FROM   dbo.Users_cx AS u --ColumnStore
	JOIN   dbo.MultiStatementTVF_Join(0) AS mj
	ON mj.UserId = u.Id
	WHERE  u.LastAccessDate >= '2016-12-01';
	
	
	SELECT u.Id, mj.*
	FROM   dbo.Users AS u --RowStore
	JOIN   dbo.MultiStatementTVF_Join(0) AS mj
	ON mj.UserId = u.Id
	WHERE  u.LastAccessDate >= '2016-12-01';

These queries each join to different tables. One ColumnStore, one not. They both get Interleaved Execution plans.

[![NUTS][3]][3]

**When does Interleaved Execution work?**

Right now, it only works with MSTVFs where the correlation is done *outside* of the function.

Here are a couple examples:

This function has no inner correlation, meaning there's no `WHERE` clause predicated on a table column and a passed in variable.

	CREATE OR ALTER FUNCTION dbo.MultiStatementTVF_Join
	(
	    @h BIGINT
	)
	RETURNS @Out TABLE
	(
	    UserId INT,
	    BadgeCount BIGINT
	)
	AS
	    BEGIN
	        INSERT INTO @Out ( UserId, BadgeCount )
	        SELECT   b.UserId, COUNT_BIG(*) AS BadgeCount
	        FROM     dbo.Badges AS b
	        GROUP BY b.UserId
	        HAVING   COUNT_BIG(*) > @h;
	        RETURN;
	    END;
	GO
	
	
This function is the opposite, with a predicate on the `UserId` column with a passed in variable.


	CREATE OR ALTER FUNCTION dbo.MultiStatementTVF_CrossApply
	(
	    @h BIGINT,
	    @id INT
	)
	RETURNS @Out TABLE
	(
	    UserId INT,
	    BadgeCount BIGINT
	)
	AS
	    BEGIN
	        INSERT INTO @Out ( UserId, BadgeCount )
	        SELECT   b.UserId, COUNT_BIG(*) AS BadgeCount
	        FROM     dbo.Badges AS b
	        WHERE    b.UserId = @id
	        GROUP BY b.UserId
	        HAVING   COUNT_BIG(*) > @h;
	        RETURN;
	    END;
	GO

It's a common misconception that `CROSS APPLY` won't work. The real limitation is noted previously. The inner-function correlation is the deal breaker.

	SELECT u.Id, mj.*
	FROM   dbo.Users AS u --RowStore
	CROSS APPLY dbo.MultiStatementTVF_Join(0) AS mj
	WHERE  mj.UserId = u.Id
	       AND u.LastAccessDate >= '2016-12-01';
	
	
	SELECT   TOP 1 u.Id, mj.*
	FROM     dbo.Users AS u --RowStore
	CROSS APPLY dbo.MultiStatementTVF_CrossApply(2147483647, u.Id) AS mj
	WHERE    u.LastAccessDate >= '2016-12-01'
	ORDER BY u.Id;

[![NUTS][4]][4]


  [1]: https://i.stack.imgur.com/O9lgW.jpg
  [2]: https://i.stack.imgur.com/q4PUA.jpg
  [3]: https://i.stack.imgur.com/RIze4.jpg
  [4]: https://i.stack.imgur.com/0R8Bq.jpg

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.