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.

How to Find Number of Times Function Called in SQL Server? - Interview Question of the Week #104 functioncalled-800x192

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)

, , , ,
Previous Post
What is Memory Grants Pending in SQL Server? – Interview Question of the Week #103
Next Post
How to Get Random Records from Table? – Interview Question of the Week #105

Related Posts

4 Comments. Leave new

  • Great for those running 2016 but what about those of is still on 2005, 2008, 2012 or 2014?

    Reply
  • Gopal Sharma
    May 4, 2020 12:26 am

    But this will not work in case of table valued function

    Reply
  • 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.

    Reply

Leave a Reply

Menu