SQL SERVER – Query to Find the Longest Running Function – Function Elapsed Time

September 22, 2018

I love my job of SQL Server Performance Tuning and Optimization consultant as every single day I walk into the customer door (well, to be honest, it is actually virtual door of GoToMeeting), I face new challenges and learn new ways to tune SQL Server. In this blog post, we will discuss Query to Find the Longest Running Function.

In SQL Server 2016, there is a new DMV dm_exec_function_stats which give details related to function execution since the last service restarts. This query will only work with SQL Server 2016 and later versions. Let us see a quick query when executed it will give us details like total worker time, logical read as well as elapsed time for the query. I have ordered this query on average elapsed time, but you can easily change the order by to your preferred order.

SELECT TOP 50
DB_NAME(fs.database_id) DatabaseName,
OBJECT_NAME(object_id, database_id) 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;

The script listed above will list all the function along with it’s execution count, logical reads, physical reads and elapsed time. This script comes very handy if your code is heavily dependant on the functions.

Note: This script only works in SQL Server 2016 and later version. Do not attempt to run this on earlier versions of SQL Server, or you will get an error.

Let me know if you find the script for Longest Running Function useful.

Reference: Pinal Dave (https://blog.sqlauthority.com)