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)
4 Comments. Leave new
Great for those running 2016 but what about those of is still on 2005, 2008, 2012 or 2014?
But this will not work in case of table valued function
Use this for scalar function.
What about tables? My requirement is like, I have been asked to create indexes for all those columns in a table, which has foreign keys but not having indexes. Of course, it is not advisable to create indexes straight away, I will have to first check is it actually necessary to create an index on that particular column or not. I am searching for a way, in which I can check how many times that particular column is called in any front-end process.