Summary: Use sys.sql_modules to identify which Scalar User-Defined Functions (UDFs) are eligible for inlining to boost query performance in SQL Server 2019.
Check if Your SQL Scalar Functions are Inlineable
When you're working with SQL Server 2019 (and later), understanding the properties of your functions can be critical for performance optimization. Scalar UDF Inlining is a game-changing feature that automatically transforms scalar functions into relational expressions.
Why Inlining Matters
This query provides insight into whether a scalar UDF can be inlined. If a function is inlineable, SQL Server essentially expands the function's logic directly into the calling query. This avoids the massive performance overhead of row-by-row function calls and context switching.
-- Identify scalar functions and their inlining status
SELECT
OBJECT_NAME(m.object_id) AS [Function Name],
m.is_inlineable,
m.inline_type,
efs.total_worker_time,
efs.execution_count
FROM sys.sql_modules AS m WITH (NOLOCK)
LEFT OUTER JOIN sys.dm_exec_function_stats AS efs WITH (NOLOCK)
ON m.object_id = efs.object_id
WHERE efs.type_desc = N'SQL_SCALAR_FUNCTION'
OR m.object_id IN (SELECT object_id FROM sys.objects WHERE type = 'FN')
OPTION (RECOMPILE);
Understanding the Results
- is_inlineable: A value of
1means the function meets the requirements for inlining. - inline_type: Indicates whether inlining is currently turned on (
1) or off (0) for that specific module.
If your function shows is_inlineable = 0, it might be due to the use of certain non-deterministic functions (like GETDATE()) or specific T-SQL constructs that prevent the optimizer from inlining the code.
For more detailed information on requirements and system views, refer to the official Microsoft documentation:
Want to speed up your queries? Identifying non-inlineable functions is the first step toward significant performance gains in SQL Server 2019!
