SQL Server Multi Statement Table Value Function (MTVFS) Performance Difference Between Versions

By:   |   Comments (2)   |   Related: > Query Plans


Problem

As a part of the SQL Server programming language, you can create user defined functions which are routines that accept different parameters to perform calculations and return the value based on the action performed. There are different types of functions supported in SQL Server: User Defined Functions and built in System Functions. As a part of user Defined Functions, you can have table value functions or scalar functions.

Table value functions can be Inline Table-Valued Functions or Multi Statement Table-Valued Functions. It has been distinguished that Inline Table-Valued Functions perform better than Multi Statement Table-Value Functions. If your code uses Multi Statement Table-Valued Functions you could have a performance bottleneck and the function can perform differently based on the SQL Server version.

Solution

We will walk through an example and show how the query plan information is different using different SQL Server compatibility levels.

Setup SQL Server Test Environment

I have SQL Server 2017 Developer Edition installed and I am using the AdventureWorks2017 database.  After downloading the database, I did a restore of the database and then created the following Table Valued Function.

USE ADVENTUREWORKS2017
GO
CREATE OR ALTER FUNCTION TEST_MTVF(@dtOrderMonth datetime)
RETURNS @orderDetail TABLE
(
   ProductID INT,
   SalesOrderID INT,
   SalesOrderNumber nvarchar(30),
   CustomerID INT,
   AccountNumber nvarchar(30),
   OrderDate datetime,
   ChrFlag char(1)

)
AS
BEGIN
   INSERT INTO @orderDetail
   select sod.ProductID,
          soh.SalesOrderID,
          soh.SalesOrderNumber,
          soh.CustomerID,
          soh.AccountNumber,
          soh.OrderDate,
          'N'
   FROM Sales.SalesOrderHeader soh
       inner join Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
   WHERE  YEAR(soh.OrderDate) = YEAR(@dtOrderMonth)

   UPDATE @orderDetail
   SET ChrFlag = 'Y'
   WHERE OrderDate < Cast(DATEADD(DAY,-1,GETUTCDATE()) AS DATE)

   RETURN

END

This is the query we will use to test each execution using different compatibility levels to see how the query plan changes with each run.

SELECT tst.CustomerID, COUNT(*) 
FROM  Sales.Customer c 
INNER JOIN dbo.TEST_MTVF('1/1/2014') tst on c.CustomerID = tst.CustomerID
INNER JOIN Production.Product prod on tst.ProductID = prod.ProductID
GROUP BY tst.CustomerID

I did not install each version of SQL Server, so what I will do is use SQL Server 2017 and then change the compatibility level for each execution, so we can see the differences.

Testing MTVF performance with compatibility level SQL Server 2012(110)

First, we will use compatibility level 110 which is SQL Server 2012 compatibility.

Change Compatibility level to 110

Now the AdventureWorks2017 database is working under SQL Server 2012 compatibility.

I will use the query above to get the total number of customers versus the sales detail using the above MTVF function. I will execute the code with the Include Actual Execution Plan, so we can look at the plan.

execution plan

After many runs of the query, I was getting results from 0-1 second. In order to get the cardinality estimation, I hover my mouse on the Table Scan [TEST_MTVF] operator.

We can see the estimated versus the actual rows. The Estimated Number of Rows = 1 and the Actual Number of Rows = 37339. The bad estimation was found when using the MTVF in SQL Server 2012 compatibility. So, the estimated 1 row could degrade performance.

execution plan

Testing MTVFS performance with compatibility level SQL Server 2014(120)

Now, I am going to change database compatibility level to SQL Server 2014.

Change Compatibility level to 120

I ran the same query several times and used the Include Actual Execution Plan option.

execution plan

Again, the query took from 0-1 second.  I hovered my mouse on the Table Scan [TEST_MTVF] operator.

execution plan

Now we are getting the Estimated Number of Rows = 100 and the Actual Number of Rows = 37339. Again, we have a bad estimation.

In SQL Server 2014, a new Cardinality Estimator (CE) was introduced. Per MSDN I summarized the improvements.

  • SQL Server introduces a new CE which is active for all databases with compatibility level of SQL Server 2014.
  • The new CE is calculating combined filter density/selectivity differently.
  • The new CE is treating ascending/descending key scenarios differently.
  • There are significant changes in how column densities of different tables in join situations are evaluated and density.
  • The different changes in calculation can end up in different plans for a query compared with the old cardinality estimation.
  • Dependent on the workload or the application used, there might be the need for more intensive testing of the new CE algorithms in order to analyze the impact on business processes.

Testing MTVFS performance with compatibility level SQL Server 2016(130)

Now, I am going to change the database compatibility level to SQL Server 2016.

Change Compatibility level to 130

I ran the same query several times and used the Include Actual Execution Plan option.

table value function performance differences sql server versions 008

The query ran in 0-1 second.  I hovered my mouse on the Table Scan [TEST_MTVF] operator.

table value function performance differences sql server versions 009

The results are Estimated Number of Rows = 100 and the Actual Number of Rows = 37339. Still getting a bad estimation. In SQL 2016, many changes were made by Microsoft to improve cardinality estimation, but I still get the same results.

Testing MTVFS performance with compatibility level SQL Server 2017(140)

I am going to change the database compatibility level to SQL Server 2017.

Change Compatibility level to 140

Again, I ran this several times with the Include Actual Execution Plan option.

table value function performance differences sql server versions 011

The query took 0-1 second to complete.  Now I am going to check the cardinality estimation by hover my mouse on Table Scan [TEST_MTVF] operator.

table value function performance differences sql server versions 012

We can see the Estimated Number of Rows = 37339 and the Actual Number of Rows = 37339. The numbers match. In SQL Server 2017, Microsoft introduced Adaptive Query Processing which has helped in this case.

Adaptive Query Processing in SQL Server 2017

Before SQL Server 2017, the SQL Server Query processing engine analyzed the query first, created the plan and executed it. Therefore, the SQL Plan created could be bad. The query processing engine was not able to change the query plan while executing the query even though we may have a performance bottleneck in some circumstances.

SQL Server 2017 introduced a new way of optimizing the SQL Server execution plan by introducing Adaptive Query Processing.

There are three parts of Adaptive Query Processing.

  • Batch mode memory grant feedback.
  • Batch mode adaptive join.
  • Interleaved execution.
Adaptive Query Processing

As per MSDN, interleaved execution 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.

AAs per our results, the MSTVFs estimation is more robust in SQL Server 2017. But the overall execution plan behaved based on the query and optimizer. The query does not guarantee to always perform well in later version. If there are still issues, first we should check statistics and if there are still issues then look at migrating the function to a Single-TVF to get better statistics and performance. Ultimately, we need to optimize the query which is in the function.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, October 29, 2018 - 5:35:14 AM - Patel Bhavesh Back To Top (78102)

Thanks Nikunj,

Execution plan is hidden in black box while you debug to MTVF. You can try to migrate first to singleTVF from MTVF if possible. If Not then convert to it in a procedure and also try for remove variable table from procedure side. Still not get the performance then will check edition because cardiantiy estimation found seperately through out instance wise. Altemetly, we need to optimize query which is reside in function or procedure side.

 


Sunday, October 28, 2018 - 11:25:11 AM - Nikunj Back To Top (78095)

Hi Bhavesh,

This is really useful info. Is plan includes user define function cost? How can I improve my user define function?

 

Thanks in Advance!! 

 















get free sql tips
agree to terms