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

Question: How to Find Number of Times Function Called in SQL Server?

Answer: This is not a real interview question, but it was asked to me during my performance tuning consultancy recently. We were troubleshooting a function which was called quite a lot and creating a huge CPU spike. Suddenly, Senior DBA asked me if there is any way we can know more details about these functions like how many times the function was called as well as what is the CPU consumption as well as IO consumption by this function. I personally think it is a very valid question.

In SQL Server 2016, there is a new DMV dm_exec_function_stats which gives 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 25
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 [total_worker_time] DESC;  

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.

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