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 ^)

Comments

Post a Comment

I Post più popolari

SQL Server, execution plan and the lazy spool (clearly explained)

Image
Hi Guys, Welcome back! In one of the last posts we talked about Table Spool . We had explained what Spool are for by telling the somewhat curious story of the halloween problem . Here we specifically talked about a type of spool called Eager Spool   You can look to the execution plan below where the eager Spool is used to avoid the halloween problem in classic query of the "salary increase".. i suggest Well, if the story of the eager spool intrigued you, today instead we will speak about another type of Spool, the Lazy Spool . The Lazy spool Let's take a small step back. So, what is a Spool operation?  As we have already understood a spool operation is simply a temporary storage of data . Data are read from a source table and stored inside a worktable in the Tempdb database . While an Eager Spool is used to prevent the Halloween problem, the Lazy Spool is instead used to store data that will later be needed again when running a query . So, when a Lazy spool is used?  ...
Read more

SQL Server, datetime vs. datetime2

Image
Hi guys, This time, in this post we will speak about datetime and datetime2 datatypes. We will talk about the pros and cons. We will talk about all the differences between the two types of data. Write me in the comments which type of data do you prefer between the two. Enjoy the reading!     Datetime Vs. Datetime2 First of all, it must be said that the datatype datetime is certainly the best known native format for storing a date in SQL Server. However, Microsoft has also been introducing a new type of data for many years now, also used to manage dates. It is in fact from SQL server 2008 that it is present in datetime2 data type. This "new" type of data was born primarily to have higher accuracy . Let's in fact talk about ... precision ! Datetime Certainly the datetime data type is not very precise and certainly there are many cases in which its precision is simply not sufficient. Let's see it with an example. Open the SQL Management studio (SSMS) and write the fo...
Read more

La clausola NOLOCK. Approfondiamo e facciamo chiarezza!

Image
Ehi ragazzi! La domanda è di quelle importanti! Siete pronti per scoprire tutto ma proprio tutto sulla clausola NOLOCK?   Se la riposta è si, allora seguitemi! Oggi ne spiegheremo il funzionamento in maniera approfondita. Faremo una introduzione al concetto di tipi di LOCK Ed infine vedremo come mai una SELECT ne può bloccare un'altra. Non vi nascondo che il motivo di questo articolo è fare chiarezza. Su questo argomento si sentono spesso affermazioni “curiose” ma tra le tante questa è la prima: “ uso la SELECT con l’opzione WITH(LOCK) così non blocco le altre tabelle ”.   Ma, è proprio vero quanto affermato? Beh oggi facciamo il punto!   Buona lettura!     La clausola NOLOCK Diciamo subito per iniziare che cos’è la NOLOCK. NOLOCK è il nome di una clausola che è possibile specificare all’interno di uno statement di tipo SELECT utilizzando la seguente sintassi: SELECT * FROM TABELLA WITH (NOLOCK) Si noti che non possiamo ...
Read more