Feb 2016 Tip of the Month – New function in SQL 2016: sys.dm_exec_function_stats

SQL Server Performance Tuning:  New function in SQL 2016: sys.dm_exec_function_stats

Returns performance statistics for cached functions. Returns information about scalar functions, including in-memory functions and CLR scalar functions but not about table valued functions. The view returns one row for each cached function plan, and the lifetime of the row is as long as the function remains in memory. When a function is removed from the cache, the corresponding plan is no longer available in the view.

SELECT [object_id],

[database_id],

OBJECT_NAME(object_id, database_id) ‘function name’,

cached_time,

       last_execution_time,

total_elapsed_time,

total_elapsed_time/execution_count AS [avg_elapsed_time],

last_elapsed_time,

       execution_count,

total_physical_reads,

total_logical_writes,

total_logical_reads,

total_worker_time

FROM sys.dm_exec_function_stats AS

ORDER BY [total_logical_reads] DESC

More information from Microsoft can be found here: https://msdn.microsoft.com/en-us/library/mt429371.aspx

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!