Rajendra Gupta
intelligent query processing (IQP) features

Scalar UDF Inlining for Azure SQL Database

February 2, 2022 by

This article explores the scalar UDF performance issues and improvements in Azure SQL Database using UDF Inlining.

Introduction

SQL Server 2017 and 2019 include intelligent query processing (IQP) features for improving query performance without modifying the code and minimum implementation efforts. The following diagram shows the IQP features and their availability in Azure SQL Database, SQL Server 2017,2019. These IQP features are as below:

  • Adaptive QP
  • Table variable deferred compilation
  • Batch mode on Rowstore
  • T-SQL scalar UDF Inlining
  • Approximate QP – Approximate count distinct

intelligent query processing (IQP) features

This article will cover the IQP feature – Scalar UDF Inlining with Azure SQL Database in detail. Let’s understand scalar UDF and its performance impact on running UDF queries.

Requirements

You should have an Azure SQL Database for executing queries. If you are not familiar with Azure, refer to SQLShack articles from the link – Azure – SQL Shack – articles about database auditing, server performance, data recovery, and more

Scalar User-Defined functions (UDF)

The T-SQL scalar user-defined function returns a single data value. It helps to build complex logic without writing complex queries. UDFs are commonly known for performance issues in most cases.

Let’s change and verify the Azure SQL Database compatibility level to SQL Server 2017(140) for the demonstration.

Note: Do not change database compatibility level unless you have a specific reason to do so. The application code might not work correctly in older compatibility levels.

database compatibility level

The following scalar function returns the sum of products quantities from the [Production].[ProductInventory] table.

In the following select statement, we refer to the UDF for retrieving the stock quantity.

Before we execute these stored procedures, enable the actual execution plan in SSMS. To enable the actual execution plan, press CTRL + M before running the query.

As shown below, the execution plan uses clustered index scan and computer scaler operator to get results using UDF.

clustered index scan and computer scaler operator

In the compute scalar, we can see the input rows as 121317. It means that the SQL Server needs to execute the UDF many times for retrieving the records.

execute the UDF To get more details of an operator, view the properties in SSMS. You can see the following query time statistics for user-defined functions.

    • UdfCpu Time
    • UdfElapsed Time

View UDF time stats

We can get the number of UDF executions using the dynamic management function (DMF) sys.dm_exec_function_stats. The function returns aggregate performance statistics for the cached function.

As per the output, it executed UDF 30997 times in my lab environment.

dynamic management function (DMF)

The scalar UDF shows a noticeable performance impact if we have a large number of affected rows, and its performance impact is due to the following reasons.

  • Lack of costing: SQL Server query optimizer does not cost the scalar operators since it is considered cheap from the cost point of view. Therefore, the UDFs are not optimized by SQL Server
  • Iterative invocation: The scalar UDFs involved iteratively; therefore, it causes additional costs due to context switching
  • Interpreted execution: The UDFs interpretation is done by statement level. Therefore, it executes the batch of statements sequentially
  • Serial execution: SQL Server does not use intra-query parallelism in queries that use UDFs

Azure SQL Database Scalar UDF Inlining

Intelligent query processing (IQP) focuses on improving query performance without you changing the code. The Scalar UDF Inlining feature transforms UDF into the scalar expressions or subqueries. Therefore, the query optimizer can generate their cost and optimize them. It optimizes the resource usage such as CPU, memory allocation, and you get results quickly since SQL does not need to execute the UDF for the ‘N’ number of rows returned in the output.

The Scalar UDF Inlining feature in Azure SQL Database of compatibility level 150 is automatically enabled. It is a database scoped feature that you can verify from the sys.database_scoped_configurations. The column is_value_default shows that this TSQL scalar UDF Inlining feature is enabled by default.

Scalar UDF Inlining

You get a different execution plan if we re-execute the query on Azure SQL Database with compatibility level 150. The query execution plan does not use compute scalar operator.

Azure SQL Database with compatibility level 150.

The new execution plan does not show the parameters – UdfCpuTime and UdfElapsedTime as seen in the plan without UDF Inlining.

Modified execution plan s

Previously, without the scalar UDF Inlining, the query optimizer could not generate the parallel plan. The following image shows a parallel plan for the Inlining query.

query optimizer parallel plan

Image Reference: Microsoft docs

Inlining scalar UDFs requirements

SQL Server can inline the scalar UDF if the following conditions are true.

  • The UDF has any of the following constructs.
    • Declare, Set
    • Select
    • If / Else
    • Return
    • UDF
    • Relational operators such as EXISTS, ISNULL
  • It does not invoke intrinsic functions such as GETDATE()
  • The UDF uses the EXECUTE AS CALLER clause
  • You do not use UDF in the ORDER BY clause
  • UDF is not a partitioned function
  • It should not refer to common table expressions (CTE)
  • The UDF should not have multiple RETURN statements
  • You cannot use encrypted columns in the UDFs
  • The UDF should not refer to remote tables or built-in views

You can refer to Scalar UDF Inlining for more details on scalar UDF Inlining requirements.

How to verify the UDF Inlining has happened or not

As stated earlier, the SQL Server transforms the UDF into a relational expression as part of UDF Inlining. If you observe the XML execution plan and search for the <UserDefinedFunction> XML node.

  • If the plan has <UserDefinedFunction> XML node, it shows Inlining has not happened. For example, the following XML plan is for SQL queries with compatibility level 140.

XML query plan

  • If there is no <UserDefinedFunction> XML node, it stats the UDF Inlining is successful.

Check UDF Inlining status

If a UDF is not inlined, you can look at the sys.sql_modules column is_inlineable after creating the scalar user-defined function. For example, the UDF [ufnGetStock ] returns value 1 that means it can benefit from UDF Inlining in Azure SQL Database.

Check sys.modules function output

Disabling Scalar UDF Inlining

Users can disable the Scalar UDF Inlining feature of Intelligent query processing as per their requirements. You can disable scalar UDF Inlining in the following ways.

  • Changing database compatibility level: The Scalar UDF Inlining feature is available for Azure SQL Database or Managed instance with compatibility level 150. You can change the compatibility level to lower, such as 140, 130 to disable the functionality

Note: You must not change the compatibility level unless for a specific requirement. It might break your code, and certain features might not work as expected.

  • Use database scoped configurations:

You can turn off the scalar UDF Inlining using the database scoped configurations on an individual database. Run the following statement on the database for which you want to disable the feature.

  • Disable UDF Inlining for a specific query

In specific requirements, you want to disable the UDF Inlining for a specific query. You can specify the query hint DISABLE_TSQL_SCALAR_UDF_INLINING as shown in the following query.

Note: The query hint always takes precedence over the compatibility level or the database scoped configuration.

  • Use the INLINE clause: We can disable scalar UDF Inlining by using the INLINE clause in the Create or Alter function, as shown below

If we have the function with INLINE=OFF, SQL Server does not perform UDF Inlining for any T-SQL that references the UDF. You can re-enable the UDF for Inlining with the INLINE=ON statement as below.

Note: The argument INLINE=ON/OFF is optional. If we do not specify it, SQL Server automatically sets it based on whether the UDF can be inlined or not.

Conclusion

This article explored the Scalar UDF Inlining for Azure SQL Database and managed instances. It improves the query performance by optimizing the way SQL Server treats UDFs. With the compatibility level 150, SQL Server transforms user-defined functions into scalar expressions or scalar subqueries. You can control the Inlining behavior at the database, query, or functions level.

Rajendra Gupta
440 Views