Get UDF execution statistics

 Get UDF execution statistics

Ever wondered which functions are consuming the most resources in your SQL Server database? The sys.dm_exec_function_stats Dynamic Management View (DMV) is your go-to for uncovering performance bottlenecks within your user-defined functions.

This powerful DMV provides insights into the execution statistics of cached functions, helping you identify those that are frequently called, take a long time to complete, or consume a lot of CPU time.

Here's a handy SQL query to retrieve the top 25 functions by total worker time, giving you a quick overview of potential performance hogs:

SELECT TOP (25) 
    DB_NAME(database_id) AS [Database Name],
    OBJECT_NAME(object_id, database_id) AS [Function Name],
    total_worker_time,
    execution_count,
    total_elapsed_time,
    total_elapsed_time / execution_count AS [avg_elapsed_time],
    last_elapsed_time,
    last_execution_time,
    cached_time,
    [type_desc]
FROM sys.dm_exec_function_stats WITH (NOLOCK)
ORDER BY total_worker_time DESC
OPTION (RECOMPILE);

This query pulls crucial metrics like the function's name, the database it belongs to, the total CPU time it has consumed (total_worker_time), how many times it's been executed (execution_count), and its average and last elapsed times. The OPTION (RECOMPILE) ensures that the query plan is recompiled each time, helping to get the most up-to-date statistics.

For more in-depth information on sys.dm_exec_function_stats, you can refer to the official Microsoft documentation: sys.dm_exec_function_stats (Transact-SQL)

Optimizing your SQL Server functions can significantly improve your database's overall performance. So go ahead, give this query a spin and see what insights you can uncover!