SQL Server, Interleaved execution feature and the multi-statement table valued function (MSTVF)

Hi Guys,

January is starting while February is coming and the days slowly begin to get longer!

Today we will talk about interleaved execution, a feature introduced in SQL Server 2017

In many previous posts they had already talked about the news that the 2017 and 2019 versions of SQL Server had brought us.

In the last article of this series we talked about adaptive join (in the photo under the blue box on the far left):  SQL Server and the Adaptive Join feature: "Adaptive Join" Vs. "wrong cardinality estimate" and "Uneven data distribution" 

Today to complete the features called "Intelling Query Processing" we will therefore talk about Interleaved execution.

Interleaved execution

Interleaved execution is a feature that is part of the Adaptive QP family of features where the acronym QP stands for Query Processing.

The term adaptive means that the execution plan is now able to adapt itself according to the data it receives as input.

In the case that we will see the execution plan is able to perform a better estimate of the cardinality by first and separately executing a part of the query.

Why was this feature developed?

This function has been mainly designed to improve and overcome a limit present in the use of
multi-statement table valued function (MSTVF).

So let's just say two words about this type of functions

Multi-statement table valued function (MSTVF).

SQL Server supports 3 types of user defined functions:

• Scalar Functions (It is a function the returns a single value)
• Inline Table Valued Functions (A function that contains a single TSQL statement and returns a Table Set)
• Multi-Statement Table Valued Functions (A function that Contains multiple TSQL statements and returns Table Set) 

An MSTVF might look like:

CREATE FUNCTION MultiStatement_TableValued_Select
(
        @date datetime
)
RETURNS 
@OutputTable TABLE
(
        Customer varchar(20) ,
        Total int
)
AS
BEGIN
  INSERT INTO @OutputTable(Customer,Total) 
  SELECT Customer, count(Customer) FROM OrdTes WHERE datedoc = @date GROUP BY Customer
     
  DELETE FROM @OutputTable WHERE year(datedoc) < 2022 -- second statement   
RETURN
END
  

If you see the part highlighted in yellow the function returns a table set

If you see the part highlighted in light blue in this case we have 2 statements, an insert into and a delete.

This above is one MSTVF.

Let's say right away that being able to write more instructions using this type of function, we have great flexibility.
However, they have a big disadvantage that leads us to say not to use them in order not to incur a significant performance penalty.

 

What's the problem with MSTVFs?

SQL Server during the execution see a MSTVF as a black box: he performs it without knowing the cost.

....and on the other hand, how do you estimate the cost of a block of statements that perhaps also contains cycles or conditional statements (IF)?

This is a big big problem from the cardinality estimates point of view! 

It can lead for example to the choice of inappropriate JOIN operators.

What was the solution developed by SQL server? 

The solution used by Microsoft was really simple.
The cost of an MSTVF is estimated as fixed.

• SQL Server 2014 gives an MSTVF a fixed cost of 1!
• SQL Server 2016 gives an MSTVF a fixed cost of 100!

  

With SQL Server 2017, Microsoft takes a step forward: interleaved execution

 

What was the idea behind interleaved execution?

Is simple and plain. 

Before executing everything the sql server statement first executes the function itself and observes how many lines it returns and uses this information instead of the cardinality estimate.
This flexibility of the execution plan is obviously a very very big step forward!

This feature is active by default as soon as the compatibility level set on the database is greater than or equal to the native one of SQL Server 2017.

Let's do a road test now!
Let's create a multi-statement table valued function and see, execution plan in hand, how the execution changes as the SQL Server version changes!

 

The Test!

Follow me and do the tests with me ... tell me if you get the same results (I love comparisons because they are the ones that help you learn the most!)

Let's create a new database and a new table with some data.

CREATE DATABASE Interleaved_demo

CREATE TABLE OrdTes (id int identity (1,1) , customer varchar(20), Product varchar(40), Qty float  primary key (id))

INSERT INTO OrdTes (customer, Product, Qty) VALUES ('Bugatti', 'Wheel', 4)
INSERT INTO OrdTes (customer, Product, Qty) VALUES ('Bugatti', 'Wheel', 2)
INSERT INTO OrdTes (customer, Product, Qty) VALUES ('Ferrari', 'Wheel', 4)

INSERT INTO OrdTes (customer, Product, Qty) VALUES ('Ferrari', 'Wheel', 1)
GO 100

INSERT INTO OrdTes (customer, Product, Qty) VALUES ('Lamborghini', 'Brake', 1)
GO 100

INSERT INTO Ordtes (customer, Product, Qty) SELECT customer, Product, Qty FROM Ordtes
GO 11
UPDATE OrdTes SET Product = Product + cast( ID % 10 as varchar(6))

And this will be our MSTFV

CREATE FUNCTION dbo.calctot()
    RETURNS @tmp TABLE (
        customer varchar(20),
        Product varchar(40),
        totQty float)
AS
BEGIN
    INSERT INTO @tmp
    SELECT
       customer,Product,sum(Qty)
    FROM
        OrdTes
    GROUP BY customer,Product

    DELETE FROM @tmp
    WHERE totQty = 0

    RETURN;
END;

If you run the command below

SELECT CUSTOMER, PRODUCT FROM CALCTOT()
GROUP BY CUSTOMER, PRODUCT

You can see that the function return 30 distinct rows:

Let's do our test..

We will execute the following statement varying the compatibility level:

SELECT O.*,T.* 
FROM DBO.CALCTOT() T
JOIN ORDTES O ON T.CUSTOMER = O.CUSTOMER
ORDER BY O.CUSTOMER

Let's start with SQL 2012 native level 110

ALTER DATABASE Interleaved_demo SET COMPATIBILITY_LEVEL = 110;

Executing the instruction and observing the execution plan we see that the estimated cardinality of our function is equal to 1 even if it returns 30 rows!

A nested loop join is used, but even if the function had returned many rows this operator would have been used .. this is a big problem ..

Let's try now with the native CL of SQL Server 2016

ALTER DATABASE Interleaved_demo SET COMPATIBILITY_LEVEL = 130;

As anticipated, in this case the estimate of cardinality was raised from 1 to 100.

The execution plan is changed and actually the cardinality is equal to 100!

A merge join is chosen instead of a nested loop which is a more suitable type of join when we need to process more than a few rows.

And finally we try the native CL of SQL Server 2017

ALTER DATABASE Interleaved_demo SET COMPATIBILITY_LEVEL = 140;

The interleaved execution feature is now actived

Let's look at the execution plan:

The real and effective cardinality is recovered!

A merge join continues to be used correctly!

Good! very very Good! but however, before writing a heavy and complex MSTVF think about it ...

That'all for today mates! I hope you enjoyed this post! 

Many are posts are in preparation, so follow me here on the blog and follow me on linkedin!

Luke

Previous post: SQL, the wildcard characters of the LIKE operator (%, _ , [] and ^)