By: Bhavesh Patel | Updated: 2018-10-25 | 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.
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.
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.
Testing MTVFS performance with compatibility level SQL Server 2014(120)
Now, I am going to change database compatibility level to SQL Server 2014.
I ran the same query several times and used the Include Actual Execution Plan option.
Again, the query took from 0-1 second. I hovered my mouse on the Table Scan [TEST_MTVF] operator.
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.
I ran the same query several times and used the Include Actual Execution Plan option.
The query ran in 0-1 second. I hovered my mouse on the Table Scan [TEST_MTVF] operator.
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.
Again, I ran this several times with the Include Actual Execution Plan option.
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.
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.
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
- Kindly test on a test server first before rolling out to production.
- Check out Adaptive Query Processing in SQL Server.
- Check out Understand the Performance Behavior of SQL Server Scalar User Defined Functions.
- Check Out Removing Function Calls for Better Performance in SQL Server.
About the author
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
Article Last Updated: 2018-10-25
