Scalar Function Statistics With sys.dm_exec_function_stats

Scalar function work can disappear behind a harmless-looking operator in the plan. The dm_exec_function_stats view gives that hidden activity its own counters. Read those counters with the cache lifetime and inlining behavior in view.

Rank Cached Function Work With dm_exec_function_stats

The view is available in SQL Server 2016 and later. It reports completed execution statistics for cached scalar function plans, rather than a permanent history. Eviction, recompilation, and server restart change the population and its accumulated counters.

I look at total CPU before focusing on the most expensive individual function call. I also compare execution counts with average duration. A cheap function repeated across a large input can become the workload's most important function.

The following query ranks current-database entries by total worker time. CPU and elapsed counters use microseconds, so the calculated columns explicitly convert them to milliseconds. The displayed totals belong to each entry's cache lifetime. On a fresh test database it returns no rows until a scalar function has run.

SELECT TOP (20)
       OBJECT_SCHEMA_NAME(f.object_id, f.database_id) AS SchemaName,
       OBJECT_NAME(f.object_id, f.database_id) AS FunctionName,
       f.cached_time, f.last_execution_time, f.execution_count,
       f.total_worker_time / 1000.0 AS TotalCpuMs,
       f.total_elapsed_time / 1000.0 AS TotalElapsedMs,
       f.total_worker_time / NULLIF(f.execution_count, 0) / 1000.0 AS AverageCpuMs,
       f.total_elapsed_time / NULLIF(f.execution_count, 0) / 1000.0 AS AverageElapsedMs,
       f.total_logical_reads, f.plan_handle
FROM sys.dm_exec_function_stats AS f
WHERE f.database_id = DB_ID()
ORDER BY f.total_worker_time DESC;

Change the ordering to execution_count when finding the busiest functions by invocation. Change it to total_elapsed_time when investigating accumulated elapsed cost. These rankings answer different questions, so do not label all three as the slowest functions.

A function with data access also deserves attention to logical reads. A pure calculation can be expensive without reading pages. Interpret each counter according to the function's definition rather than assuming every scalar UDF has the same cost pattern.

Verify That Statistics Collection Is Enabled

SQL Server 2022 and later expose EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS as a database scoped configuration. It controls whether scalar UDF execution statistics appear in this view and defaults to ON. Azure SQL Database and Managed Instance also support it.

Collecting statistics can add overhead to intensive scalar-UDF workloads. Inspect the setting before interpreting an empty result, and document any intentional disablement. Enabling collection does not recover executions from an earlier period without collection.

SELECT name, value
FROM sys.database_scoped_configurations
WHERE name IN
(N'EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS', N'TSQL_SCALAR_UDF_INLINING');
-- SQL Server 2022 or later, only when collection is intentionally needed.
ALTER DATABASE SCOPED CONFIGURATION
SET EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS = ON;

Use that ALTER statement only on a supported engine and with authorization for the configuration change. Record the previous value and restore it after a bounded experiment if appropriate. Do not disable a beneficial execution feature simply to obtain a more familiar monitoring row.

On SQL Server 2022 and later, the view requires VIEW SERVER PERFORMANCE STATE. Earlier SQL Server releases use VIEW SERVER STATE. Azure SQL permission and visibility rules differ, so use the documented requirement for your deployment.

Establish a Small Non-Inlined Example

The demonstration uses SQL Server 2019 or later because INLINE is part of its scalar UDF inlining controls. Run it in a test database. The first definition explicitly disables inlining so separate function execution remains visible to the monitoring path.

CREATE OR ALTER FUNCTION dbo.ServiceFee
(
    @Amount decimal(12,2)
)
RETURNS decimal(12,2)
WITH INLINE = OFF
AS
BEGIN
    RETURN CONVERT(decimal(12,2), @Amount * 0.02);
END;
GO
DROP TABLE IF EXISTS #FeeInput;
CREATE TABLE #FeeInput
(ItemId int PRIMARY KEY, Amount decimal(12,2) NOT NULL);
INSERT #FeeInput VALUES (1,100),(2,250),(3,800),(4,1200);
SELECT ItemId, dbo.ServiceFee(Amount) AS CalculatedFee
FROM #FeeInput
ORDER BY ItemId;
GO
SELECT execution_count, total_worker_time, total_elapsed_time, cached_time
FROM sys.dm_exec_function_stats
WHERE database_id = DB_ID() AND object_id = OBJECT_ID(N'dbo.ServiceFee');

The input is deliberately small so you can verify the calculation's meaning. Increase work only within a controlled test budget when investigating collection overhead. On my run, the view reported an execution count of 4, one call per input row.

Do not clear the whole server's plan cache to tidy this experiment. Filter the function's entry and note its cached_time instead. Monitoring should explain the workload, not create an unrelated compile surge.

Connect the Function to Candidate Callers

The function statistics row does not identify every parent query that invoked it. Its sql_handle can relate to statements executed inside the function, not a complete outer-call history. Avoid treating a handle match as an exact list of callers.

Static dependencies help locate stored modules containing explicit references. The next query finds references to the demonstration function. Dynamic SQL, unresolved references, and external application statements need separate investigation.

SELECT OBJECT_SCHEMA_NAME(d.referencing_id) AS ReferencingSchema,
       OBJECT_NAME(d.referencing_id) AS ReferencingObject,
       o.type_desc
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o ON o.object_id = d.referencing_id
WHERE d.referenced_id = OBJECT_ID(N'dbo.ServiceFee');
SELECT TOP (20) q.execution_count, q.total_worker_time,
       q.total_elapsed_time, t.text AS CandidateText, p.query_plan
FROM sys.dm_exec_query_stats AS q
CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) AS t
OUTER APPLY sys.dm_exec_query_plan(q.plan_handle) AS p
WHERE CHARINDEX(N'dbo.ServiceFee', t.text) > 0
  AND CHARINDEX(N'sys.dm_exec_query_stats', t.text) = 0
ORDER BY q.total_worker_time DESC;

In my test, the dependency query returned nothing, because no stored module calls the function. The text search listed the demo batch and also the monitoring query that names the function. Text matches produce candidates rather than proof of function execution. Comments and unused statements can contain the same name. Open the relevant statement plan and compare workload activity over the interval you are investigating.

Caller and function timings are not independent bills you can simply add together. The outer query's elapsed duration already includes the wait for its function work. Attribution needs consistent scope and evidence, not a sum of attractive totals.

Understand Why Inlining Hides Rows From dm_exec_function_stats

SQL Server 2019 introduced scalar UDF inlining at database compatibility level 150 and above. Eligible function logic can become part of the calling relational expression. The separately invoked function then disappears from the function statistics view for that inlined execution.

A missing row therefore does not establish zero cost. Inspect the caller's plan and counters when work has been inlined. Table-valued functions are also outside this view's reported scalar population.

CREATE OR ALTER FUNCTION dbo.ServiceFee
(
    @Amount decimal(12,2)
)
RETURNS decimal(12,2)
WITH INLINE = ON
AS
BEGIN
    RETURN CONVERT(decimal(12,2), @Amount * 0.02);
END;
GO
SELECT o.name, m.is_inlineable, m.inline_type
FROM sys.objects AS o
JOIN sys.sql_modules AS m ON m.object_id = o.object_id
WHERE o.object_id = OBJECT_ID(N'dbo.ServiceFee');
SELECT ItemId, dbo.ServiceFee(Amount) AS CalculatedFee
FROM #FeeInput
ORDER BY ItemId;
GO

The module's eligibility and enabled status do not prove that this particular query was inlined. Inspect the actual calling plan. On my run, the statistics query from the earlier example returned no row for the function after this inlined call. Inlined logic lacks the separate UserDefinedFunction node, while restrictions in the calling context can prevent the transformation.

Keep the installed cumulative update in your test record because eligibility rules and fixes have evolved. Compare results for correctness before comparing resource use. A changed plan is useful only when it preserves the function's intended behavior.

Compare Intervals Rather Than Different Cache Ages

Take two snapshots when you need activity for one troubleshooting interval. Match database, object, plan handle, and cached_time, then subtract nondecreasing counters. Exclude replaced or missing entries instead of interpreting resets as negative activity.

I retain the caller plan with the function snapshot when investigating repeated scalar work. I also look for a set-based rewrite that removes unnecessary repeated calls. A function name can hide a surprising amount of repetition behind a pleasantly short SELECT.

Which caller invokes the function most, and was its work inlined during that interval? Answer both before changing the definition. The dm_exec_function_stats counters are a starting point for that investigation, not a complete performance verdict.

An interval from dm_exec_function_stats should include its collection timestamps and matching caller evidence. Preserve entries that reset as an explicit coverage gap. A missing cache row cannot be treated as a measured zero for that function during the entire interval.

Related reading on this blog: Scalar UDF Inlining: When Old Functions Suddenly Get Fast and Scalar Functions and Performance.

A missing function counter is not proof of free work, it is a reason to inspect the execution path.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.