SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

 

 


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

All About SQL

I am a Senior DBA with interest in MS technology especially SQL Server and Azure. During 2015 I was mentored by Paul Randal – Data Platform (SQL Server) MVP and during 2016 I completed my SQLskills Immersion training on Internals and Performance Tuning. When I am not working I am in the gym burning calories.

Comments

Leave a comment on the original post [blobeater.blog, opens in a new window]

Loading comments...