Introduction

Hey there! Have you ever found yourself scratching your head, wondering how to squeeze every last drop of performance out of your queries? Well, I’ve got a treat for you today. We’re going to dive into the world of TSQL_SCALAR_UDF_INLINING and explore how this little gem can help you optimize your SQL Server performance. Get ready to learn when to turn it on, when to turn it off, and how to make the most of this powerful feature!

What is TSQL_SCALAR_UDF_INLINING?

First things first, let’s break down what TSQL_SCALAR_UDF_INLINING actually is. In simple terms, it’s a feature introduced in SQL Server 2019 that allows the query optimizer to inline scalar user-defined functions (UDFs) into the calling query. This means that instead of executing the UDF separately for each row, the function’s logic is incorporated directly into the query execution plan.

Now, you might be thinking, “Why is this such a big deal?” Well, my friend, inlining scalar UDFs can lead to significant performance improvements by reducing the overhead associated with function calls and enabling better query optimization.

When to Enable TSQL_SCALAR_UDF_INLINING

So, when should you consider enabling TSQL_SCALAR_UDF_INLINING? Here are a few scenarios where it can be particularly beneficial:

1. When you have queries that heavily rely on scalar UDFs
2. When your scalar UDFs are relatively simple and don’t contain complex logic or external dependencies
3. When you want to improve the performance of queries that are currently suffering due to the overhead of scalar UDF calls

Let me share a quick example. Imagine you have a query that calculates the total sales for each customer, and you’ve defined a scalar UDF to apply a discount based on the customer’s loyalty status. By enabling TSQL_SCALAR_UDF_INLINING, the query optimizer can inline the discount calculation directly into the query, eliminating the need for separate function calls and potentially boosting performance.

To enable TSQL_SCALAR_UDF_INLINING for a specific scalar UDF, you can use the WITH clause when creating or altering the function. Here’s an example:

CREATE OR ALTER FUNCTION dbo.GetDiscountedPrice(@price DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
WITH INLINE = ON
AS
BEGIN
    RETURN @price * 0.9; -- Apply a 10% discount
END;

In this example, we create (or alter) a scalar UDF named GetDiscountedPrice that takes a price as input and applies a 10% discount. By specifying WITH INLINE = ON, we enable TSQL_SCALAR_UDF_INLINING for this function.

You can also enable TSQL_SCALAR_UDF_INLINING at the database level by setting the TSQL_SCALAR_UDF_INLINING database scoped configuration:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

This statement enables TSQL_SCALAR_UDF_INLINING for all eligible scalar UDFs in the current database.

When to Disable TSQL_SCALAR_UDF_INLINING

Now, before you go and enable TSQL_SCALAR_UDF_INLINING for every scalar UDF in your database, there are a few situations where you might want to think twice:

1. When your scalar UDFs contain complex logic or external dependencies that can’t be easily inlined
2. When inlining the scalar UDF would result in a less efficient query execution plan
3. When you need to maintain compatibility with older versions of SQL Server

In these cases, it’s best to leave TSQL_SCALAR_UDF_INLINING disabled and explore alternative optimization techniques.

To disable TSQL_SCALAR_UDF_INLINING for a specific scalar UDF, you can use the WITH clause and set INLINE = OFF:

CREATE OR ALTER FUNCTION dbo.GetDiscountedPrice(@price DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
WITH INLINE = OFF
AS
BEGIN
    RETURN @price * 0.9; -- Apply a 10% discount
END;

In this example, we explicitly disable TSQL_SCALAR_UDF_INLINING for the GetDiscountedPrice function by specifying WITH INLINE = OFF.

Similarly, you can disable TSQL_SCALAR_UDF_INLINING at the database level by setting the TSQL_SCALAR_UDF_INLINING database scoped configuration to OFF:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

This statement disables TSQL_SCALAR_UDF_INLINING for all scalar UDFs in the current database.

Checking the Status of TSQL_SCALAR_UDF_INLINING

To check the current status of TSQL_SCALAR_UDF_INLINING for a specific scalar UDF, you can query the sys.sql_modules system view:

SELECT 
    o.name AS FunctionName,
    m.is_inlineable
FROM sys.objects o
JOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE o.type = 'FN' AND o.name = 'GetDiscountedPrice';

This query retrieves the name of the scalar UDF and the value of the is_inlineable column, which indicates whether TSQL_SCALAR_UDF_INLINING is enabled (1) or disabled (0) for that function.

To check the status of TSQL_SCALAR_UDF_INLINING at the database level, you can query the sys.database_scoped_configurations system view:

SELECT 
    configuration_id,
    name,
    value
FROM sys.database_scoped_configurations
WHERE name = 'TSQL_SCALAR_UDF_INLINING';

This query retrieves the current value of the TSQL_SCALAR_UDF_INLINING database scoped configuration, where 1 represents enabled and 0 represents disabled.

Conclusion

Alright, folks, we’ve covered a lot of ground today! We’ve learned about TSQL_SCALAR_UDF_INLINING, when to enable it, and when to disable it. By understanding how this feature works and applying it judiciously, you can take your SQL Server performance to new heights.

Remember, optimization is an ongoing journey, and TSQL_SCALAR_UDF_INLINING is just one tool in your arsenal. Keep exploring, keep learning, and most importantly, keep having fun with SQL Server!

Until next time, happy querying!

Link to learn more: Scalar UDF Inlining