I worked on testing interleaved execution with Microsoft back in January, I didn’t do much, just tested the functionality against some in-house code we had. (If you need a detailed primer on the subject, please see https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/19/introducing-interleaved-execution-for-multi-statement-table-valued-functions/)

As the article states it “changes the unidirectional boundary between the optimization and execution phases for a single-query execution and enables plans to adapt based on the revised estimates. During optimization if we encounter a candidate for interleaved execution, which for this first version will be multi-statement table valued functions (MSTVFs), we will pause optimization, execute the applicable subtree, capture accurate cardinality estimates and then resume optimization for downstream operations”.

So what I really would like to do now is show it in action.

The workload

I have read-only T-SQL that references the MSTVF. I did have some code that use both data modifications and cross apply but interleaved execution does not occur in those scenarios.

So on my SQL Server 2017 instance I set the database to 110 compatibility mode and set query store on where then I execute my code.

ALTER DATABASE MSCRM SET COMPATIBILITY_LEVEL = 110
ALTER DATABASE MSCRM SET QUERY_STORE = ON;  

Its best to see what is happening with some snippets of the execution plan, it is way too big to post here. How do you know you have an interleaved candidate? The plan will tell you.

More specifically within the SELECT properties where  ContainsInterleavedExecution =True.

If you look at the table scan – its estimation is 1. Which is what you would expect for SQL Server 2012.

Switching to 140 level. I expect to see evidence of interleaved execution actually taking place.

ALTER DATABASE MSCRM SET COMPATIBILITY_LEVEL = 140

Don’t forget, we expect to see a change in the table cardinality too.

However, on this occasion I see a performance decrease.

Plan ID 6 being where the interleaved execution takes place, the question is why is there plan regression?

I thought I was onto something so I contacted Joe Sack from Microsoft after some analysis and comparisons across the execution plans this was not a regression due to interleaved execution but more so down to the new CE (Cardinality Estimator). So I learned something and executed the following.

ALTER DATABASE MSCRM SET COMPATIBILITY_LEVEL = 140
GO
ALTER DATABASE SCOPED CONFIGURATION  SET LEGACY_CARDINALITY_ESTIMATION=ON ;

<RUN THE WORKLOAD>

The query took 16 seconds. Interleaved execution is still available to you even if you use the legacy CE.

That’s the learning point of this blog post –Interleaved execution is still available to you even if you use the legacy CE.

Filed under: SQL SERVER, SQL Server 2017 Tagged: CE, Interleaved Execution, QueryStore, SQL server, SQL Server 2017