As a SQL Server Performance Tuning and Optimization consultant, I am constantly faced with new challenges and opportunities to learn. One of the key aspects of my job is to identify and optimize the performance of SQL Server queries. In this blog post, I will discuss a query that can help you find the longest running function in SQL Server.

In SQL Server 2016 and later versions, a new Dynamic Management View (DMV) called dm_exec_function_stats provides details related to function execution since the last service restarts. By utilizing this DMV, we can gather information such as total worker time, logical reads, and elapsed time for a specific query.

Here is a sample query that can be executed to retrieve the details of the longest running function:

SELECT TOP 50
    DB_NAME(fs.database_id) AS DatabaseName,
    OBJECT_NAME(object_id, database_id) AS FunctionName,
    fs.cached_time, fs.last_execution_time, fs.total_elapsed_time,
    fs.total_worker_time, fs.total_logical_reads, fs.total_physical_reads,
    fs.total_elapsed_time / fs.execution_count AS [avg_elapsed_time],
    fs.last_elapsed_time, fs.execution_count
FROM sys.dm_exec_function_stats AS fs
ORDER BY [avg_elapsed_time] DESC;

When executed, this query will list all the functions along with their execution count, logical reads, physical reads, and elapsed time. It is particularly useful if your code heavily relies on functions.

It is important to note that this script only works in SQL Server 2016 and later versions. If you attempt to run it on earlier versions, you will encounter an error.

I hope you find this script for finding the longest running function useful in your SQL Server performance tuning and optimization efforts. Feel free to reach out to me if you have any questions or suggestions.

Happy querying!