SQL Server 2017 – Interleaved Execution

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.

ThePlan

More specifically within the SELECT properties where  ContainsInterleavedExecution =True.

IsTrue

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

table1

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

ALTER DATABASE MSCRM SET COMPATIBILITY_LEVEL = 140

ExecTrue

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

Table21

However, on this occasion I see a performance decrease.

QSmain

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.

 

 

One thought on “SQL Server 2017 – Interleaved Execution

  1. Pingback: Interleaved Execution And Compatibility Levels – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s