During a recent performance tuning consultancy, I was asked a question about finding the number of times a function was called in SQL Server. This question is not commonly asked in interviews, but it is a valid concern when troubleshooting performance issues.

In SQL Server 2016 and later versions, there is a new Dynamic Management View (DMV) called dm_exec_function_stats that provides details about function execution since the last service restarts. By using this DMV, we can gather information such as the number of times the function was called, CPU consumption, and IO consumption.

Here is a sample query that utilizes the dm_exec_function_stats DMV to retrieve details about function execution:

SELECT TOP 25
    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 total_worker_time DESC;

Note that this query is specific to SQL Server 2016 and later versions. It will not work on earlier versions of SQL Server, and attempting to run it will result in an error.

By executing this query, you will get a result set that includes the database name, function name, cached time, last execution time, total elapsed time, total worker time, total logical reads, total physical reads, average elapsed time per execution, last elapsed time, and execution count. You can modify the ORDER BY clause to sort the results based on your preferred criteria.

Using the dm_exec_function_stats DMV, you can gain valuable insights into the performance of your functions and identify any potential bottlenecks. This information can help you optimize your code and improve overall system performance.

Remember to only use this query on SQL Server 2016 and later versions to avoid any compatibility issues.