Gå til indhold

Functions in SQL Server 2019

I recently tested how the performance differed on SQL 2017 and SQL 2019 with table variables. This time, I will look at scalar valued functions, which have also been improved in SQL 2019.
Billede af Unit IT

Unit IT

SQL
I recently tested how the performance differed on SQL 2017 and SQL 2019 with table variables. This time, I will look at scalar valued functions, which have also been improved in SQL 2019.
Functions I SQL Server before 2019, Compatability level 140 or less, was not costed, and they were executed once for every matching row. Although the plan for the function is cached and reused, it still did not help the performance much. The scalar valued function is not considered as a part of the whole plan.
This has changed in SQL Server 2019 – with the new Intelligent Query Processing feature, scalar valued functions will be inlined and handled as part of the whole query, making it much more efficient to run queries with functions.
This new feature will “expand” and replace the function in the calling query with a “sub-query”
 
Let’s look at a simple test and note the different ways SQL servers 2017 and 2019 handle scalar valued functions.
I will write a Scalar value function and test it against SQL servers 2017 and 2019. I will look at the execution plans, statistics IO and Time. The functions:
CREATE 
FUNCTION GetAddress (@BId INT)
RETURNS NVARCHAR(50)
AS
BEGIN
RETURN
(
SELECt TOP 1 CAST(CONCAT(A.AddressLine1 , ' ' ,A.PostalCode, ' ' , A.City) AS  NVARCHAR(50)) AS AddressString
FROM Person.Address A INNER JOIN [Person].[BusinessEntityAddress] BEI ON BEI.AddressID = A.AddressID
WHERE BEI.BusinessEntityID = @BId 
)
END
I will run these scripts on both versions.
SELECT 
    p.FirstName,
    P.LastName,
    P.Title,
    dbo.GetAddress(p.BusinessEntityID)    
FROM Person.Person P
 
Later on I will look at the cached plans using this query:
DBCC FREEPROCCACHE
GO
SELECT 
    p.FirstName,
    P.LastName,
    P.Title,
    dbo.GetAddress(p.BusinessEntityID)
    
FROM Person.Person P
GO
SELECT Text, 
       C.cacheobjtype, 
       C.objtype, 
       S.execution_count,
       S.total_elapsed_time,
       S.total_logical_reads,
       (S.total_worker_time*1.0) / POWER(10,6) total_worker_sec
       
FROM sys.dm_exec_cached_plans  C 
inner join sys.dm_exec_query_stats S on S.plan_handle = C.plan_handle
CROSS APPLY sys.dm_exec_sql_text(S.plan_handle)  
WHERE dbid = db_id()
For now, lets look at the execution of the query:
SELECT 
    p.FirstName,
    P.LastName,
    P.Title,
    dbo.GetAddress(p.BusinessEntityID)    
FROM Person.Person P
On the 2 versions, first lets compare statistics IO.

2019-02-21-11_40_27-functions-in-sql-server-2019-_-sqld3velop3r-google-chrome

Note that in SQL 2017 the only table in the statistics is Person, but we know that the function uses Address and BusinessEntityAddress. Look at the result in SQL 2019 here the function is inlined and we get a read of the tables used in the function. Lets look at execution times:

2019-02-21-11_40_43-functions-in-sql-server-2019-_-sqld3velop3r-google-chrome

There is some difference. The execution plan for SQL Server 2017 look like this:

2019_executionplan_functions

Note the compute scalar with a cost of 0 – it is the function that is applied on every row, one row at a time.

Now lets look in the plan cache, look at the execution_count of the function, and the total_worker_sec:

2017_functions_in_plancache

I would argue that the Scalar function’s “cost” is more than 0, but that’s the way the optimizer is designed to handle scalar functions at this compatibility level. Let’s look at how the execution plan looks in SQL Server 2019:

2019_executionplan_functions

Here, there is no doubt that the function is inlined as a part of the requesting query And note in the plan cache, only one query:

2019_functions_in_plancache

A new column in the sys.sql_modules “is_inlinable” tells us that the scalar valued function in 2019 can be inlined, and thereby handled by the optimizer and executed a lot faster. Running this query:

2019_sys_modules_inlinable

Thanks for reading. If you have any questions, troubles with your SQL server, queries, or environment, please don’t hesitate to call me or one of my colleagues at Unit IT phone: 88 333 333.

Relaterede indlæg