How to Find Number of Times Function Called in SQL Server? – Interview Question of the Week #104

Question: How many times has a SQL Server function run, and how much CPU has it used?

This was not really an interview question. A senior DBA asked me during a performance tuning engagement. We had a function that seemed to be called repeatedly during a CPU spike, and the team wanted a number instead of a guess. It is exactly the kind of practical question an interview ought to reward.

On SQL Server 2016 and later, sys.dm_exec_function_stats reports execution and resource totals for cached scalar functions. Start with the functions that consumed the most CPU:

SELECT TOP (25)
       DB_NAME(fs.database_id) AS DatabaseName,
       OBJECT_SCHEMA_NAME(fs.object_id, fs.database_id) AS SchemaName,
       OBJECT_NAME(fs.object_id, fs.database_id) AS FunctionName,
       fs.execution_count AS ExecutionCount,
       fs.total_worker_time / 1000000.0 AS TotalCPUSeconds,
       fs.total_worker_time / NULLIF(fs.execution_count, 0) / 1000.0
           AS AvgCPUMilliseconds,
       fs.total_logical_reads AS TotalLogicalReads,
       fs.total_elapsed_time / 1000000.0 AS TotalElapsedSeconds,
       fs.cached_time AS CachedAt,
       fs.last_execution_time AS LastExecutedAt
FROM sys.dm_exec_function_stats AS fs
ORDER BY fs.total_worker_time DESC;

Inspecting other sessions’ function statistics requires VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later, or VIEW SERVER STATE on earlier versions.

The time counters are in microseconds, so the divisions display seconds or milliseconds. execution_count covers executions since that function plan was last compiled, while the plan remained cached. These are completed executions, not calls currently in flight. A restart, recompile, or cache eviction can reset what this view can show.

One modern caveat matters: this view does not return table-valued functions, and it does not separately report scalar functions that SQL Server inlined into the calling query. An absent row is therefore not proof that a function was never called. Check the calling query and its plan, especially on SQL Server 2019 and later where scalar UDF inlining may apply.

When the count is high, compare total CPU with average CPU. A tiny function called many times can be expensive in total; a function called only a few times may instead have costly individual executions. That distinction tells you where to investigate next.

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