sys.dm_exec_function_stats - Usage & Examples

SQL Server's Query Execution Engine maintains execution statistics for user-defined functions in memory structures that persist until instance restart or manual clearing. When a function executes, the engine records metrics in sys.dm_exec_function_stats including total worker time (CPU), elapsed time, logical reads, writes, and execution count.

The DMV aggregates statistics at the function level, not individual execution level. Each row represents one function identified by object_id and type_desc. Statistics accumulate from the SQL Server Buffer Pool Manager, Lock Manager, and I/O completion routines. The Query Processor updates these counters after each function execution completes.

SQL Server 2016 introduced significant changes to function execution tracking, particularly for inline table-valued functions which may not appear in this DMV when successfully inlined by the optimizer. SQL Server 2019 enhanced scalar function performance through inlining capabilities, reducing entries in this DMV for functions that get inlined into the calling query. SQL Server 2022 maintains the same structure but with improved accuracy in multi-threaded scenarios.

The execution_count represents successful completions only. Failed executions due to errors, timeouts, or cancellations do not increment this counter but may still consume CPU cycles recorded in total_worker_time. Memory grants for functions are tracked separately and do not appear in this DMV.

-- Identify functions with highest CPU consumption
SELECT TOP 20 
    OBJECT_NAME(object_id) AS function_name,
    type_desc,
    execution_count,
    total_worker_time,
    avg_worker_time = total_worker_time / execution_count,
    total_elapsed_time,
    avg_elapsed_time = total_elapsed_time / execution_count
FROM sys.dm_exec_function_stats
ORDER BY total_worker_time DESC;

-- Find functions with excessive execution counts (potential N+1 problems)
SELECT TOP 20
    OBJECT_NAME(object_id) AS function_name,
    type_desc,
    execution_count,
    total_logical_reads,
    avg_logical_reads = total_logical_reads / execution_count,
    last_execution_time
FROM sys.dm_exec_function_stats
WHERE execution_count > 100000
ORDER BY execution_count DESC;

-- Detect functions causing blocking or resource contention
SELECT TOP 15
    OBJECT_NAME(object_id) AS function_name,
    type_desc,
    execution_count,
    total_elapsed_time,
    total_worker_time,
    -- High ratio indicates blocking/waiting
    blocking_ratio = CAST(total_elapsed_time AS FLOAT) / NULLIF(total_worker_time, 0),
    last_execution_time
FROM sys.dm_exec_function_stats
WHERE total_elapsed_time > total_worker_time * 2
ORDER BY blocking_ratio DESC;

-- Analyze I/O intensive functions
SELECT TOP 20
    OBJECT_NAME(object_id) AS function_name,
    type_desc,
    execution_count,
    total_logical_reads + total_logical_writes AS total_logical_io,
    avg_logical_io = (total_logical_reads + total_logical_writes) / execution_count,
    total_physical_reads,
    avg_physical_reads = total_physical_reads / execution_count
FROM sys.dm_exec_function_stats
WHERE total_logical_reads + total_logical_writes > 1000
ORDER BY total_logical_reads + total_logical_writes DESC;

-- Compare recent vs historical function performance
SELECT 
    OBJECT_NAME(object_id) AS function_name,
    execution_count,
    last_execution_time,
    cached_time,
    -- Days since function was compiled
    days_cached = DATEDIFF(day, cached_time, GETDATE()),
    -- Average executions per day
    avg_executions_per_day = execution_count / NULLIF(DATEDIFF(day, cached_time, GETDATE()), 0)
FROM sys.dm_exec_function_stats
WHERE last_execution_time > DATEADD(hour, -24, GETDATE())
ORDER BY avg_executions_per_day DESC;

Clear function execution statistics for analysis reset

-- Clear all function execution statistics
-- WARNING: This removes ALL historical function performance data
-- Run during maintenance window and ensure monitoring captures new baseline
DBCC FREEPROCCACHE;

-- Alternative: Clear specific function statistics (SQL Server 2016+)
-- DBCC FREEPROCCACHE (0x[plan_handle_from_sys.dm_exec_function_stats]);

This script resets all function execution statistics, useful when implementing function optimizations and need clean metrics for comparison. Test in development first as this affects all cached plans.

Create monitoring table for historical function statistics

-- Create permanent tracking table for function performance trends
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'function_stats_history')
BEGIN
    CREATE TABLE dba.function_stats_history (
        capture_date datetime2 NOT NULL DEFAULT GETDATE(),
        function_name nvarchar(128),
        type_desc nvarchar(60),
        execution_count bigint,
        total_worker_time bigint,
        avg_worker_time bigint,
        total_logical_reads bigint,
        INDEX IX_function_stats_history_date_function CLUSTERED (capture_date, function_name)
    );
END;

-- Insert current statistics (run via SQL Agent job every hour)
INSERT INTO dba.function_stats_history (
    function_name, type_desc, execution_count, 
    total_worker_time, avg_worker_time, total_logical_reads
)
SELECT 
    OBJECT_NAME(object_id),
    type_desc,
    execution_count,
    total_worker_time,
    total_worker_time / execution_count,
    total_logical_reads
FROM sys.dm_exec_function_stats
WHERE execution_count > 0;

Expected impact: Provides historical trending data for function performance analysis. No performance impact on production workloads.

Identify functions candidates for inlining optimization

-- Generate report of scalar functions that may benefit from inlining (SQL Server 2019+)
WITH function_analysis AS (
    SELECT 
        OBJECT_NAME(f.object_id) AS function_name,
        f.execution_count,
        f.avg_worker_time,
        f.total_logical_reads,
        m.definition,
        -- Simple heuristics for inlining candidates
        CASE 
            WHEN f.avg_worker_time < 1000 AND LEN(m.definition) < 2000 
                AND m.definition NOT LIKE '%CURSOR%' 
                AND m.definition NOT LIKE '%WHILE%'
            THEN 'INLINE_CANDIDATE'
            ELSE 'REVIEW_REQUIRED'
        END AS optimization_recommendation
    FROM sys.dm_exec_function_stats f
    INNER JOIN sys.sql_modules m ON f.object_id = m.object_id
    WHERE f.type_desc = 'SQL_SCALAR_FUNCTION'
        AND f.execution_count > 1000
        AND f.avg_worker_time > 100
)
SELECT 
    function_name,
    execution_count,
    avg_worker_time,
    total_logical_reads,
    optimization_recommendation
FROM function_analysis
ORDER BY execution_count * avg_worker_time DESC;

This analysis identifies scalar functions that consume significant resources and may benefit from rewriting as inline table-valued functions or direct T-SQL. Review each candidate manually before implementing changes.

Enable Query Store in all databases to complement sys.dm_exec_function_stats with query-level performance data. Query Store retains historical statistics beyond instance restarts, providing better trending analysis than this DMV alone.

Configure Extended Events to capture function execution details for problematic functions. Create targeted sessions monitoring sp_statement_completed events filtered by object_id for specific functions showing performance degradation.

Implement automated monitoring comparing current function performance against historical baselines. Alert when execution_count increases beyond normal patterns or avg_worker_time exceeds established thresholds by more than 200%.

Use SQL Server 2019+ scalar function inlining features by ensuring compatibility_level is 150 or higher. Monitor sys.dm_exec_function_stats after enabling inlining to confirm performance improvements and reduced function execution counts.

Establish coding standards requiring table-valued functions instead of scalar functions for complex operations. Scalar functions execute row-by-row and cannot be parallelized, while inline table-valued functions integrate into query execution plans more efficiently.

Create quarterly reviews of functions appearing in the top 20 CPU consumers. Many function performance issues stem from N+1 query patterns where functions execute thousands of times per query rather than fundamental function inefficiency.