MS SQL DMVs for UDF performance stats - how to find top 10 worst UDFs

Question

I heard in Microsoft SQL Server there are multiple ways to find "worst" stored procedures: by number of executions, by CPU worker time, by queue wait time etc.

I am looking for a way to find worst (slowest / most used) UDFs -- is there a DMV query for that?

For example this query from Five DMV Queries That Will Make You a Superhero returns top 25 cached SPs by total worker time, which effectively means most expensive stored procedures for CPU pressure.

How do I write something like this for UDFs?

SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], 
qs.total_worker_time/qs.execution_count AS [AvgWorkerTime], qs.execution_count, 
ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GETDATE()), 0) AS [Calls/Second],
qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count 
AS [avg_elapsed_time], qs.cached_time
FROM sys.procedures AS p
INNER JOIN sys.dm_exec_procedure_stats AS qs
ON p.[object_id] = qs.[object_id]
WHERE qs.database_id = DB_ID()
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);

score 2 · Answer 1 · answered May 12 '11 at 02:53

This is another way to find the TOP 100 worst performing SQL Statement with the name of object that contain those statements e.g. Stored Procedure, Trigger and Function. This runs in context, so you will need to switch your SSMS to the DB you want to run this on, or put the USE DB statment. Also, if you restart your DB, the DMV stats will reset, so it may return '0' rows...


SELECT  TOP 100
            [Object_Name] = object_name(st.objectid),
            creation_time, 
            last_execution_time, 
            total_cpu_time = total_worker_time / 1000, 
            avg_cpu_time = (total_worker_time / execution_count) / 1000,
            min_cpu_time = min_worker_time / 1000,
            max_cpu_time = max_worker_time / 1000,
            last_cpu_time = last_worker_time / 1000,
            total_time_elapsed = total_elapsed_time / 1000 , 
            avg_time_elapsed = (total_elapsed_time / execution_count) / 1000, 
            min_time_elapsed = min_elapsed_time / 1000, 
            max_time_elapsed = max_elapsed_time / 1000, 
            avg_physical_reads = total_physical_reads / execution_count,
            avg_logical_reads = total_logical_reads / execution_count,
            execution_count, 
            SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
                  (
                        (
                              CASE statement_end_offset
                                    WHEN -1 THEN DATALENGTH(st.text)
                                    ELSE qs.statement_end_offset
                              END 
                              - qs.statement_start_offset
                        ) /2
                  ) + 1
            ) as statement_text
FROM 
            sys.dm_exec_query_stats qs
CROSS APPLY 
            sys.dm_exec_sql_text(qs.sql_handle) st
WHERE
            Object_Name(st.objectid) IS NOT NULL
            AND st.dbid = DB_ID()
ORDER BY 
            db_name(st.dbid), 
            total_worker_time / execution_count  DESC

score 2 · Accepted Answer · answered May 14 '11 at 11:48

2

Try this.

changing sys.procedures to sys.sql_modules
add further join to sys.objects
filter by sys.objects.type IN ('IF', 'TF', 'FN')

The code above restricts to stored procs only

Note: there is no "sys.functions"

answered May 14 '11 at 11:48

gbn

422,506
82
585
676

score 0 · Answer 3 · answered Oct 25 '21 at 06:14

SQL Server 2016 onwards (and SQL Azure) have DMV sys.dm_exec_function_stats:

Returns information about scalar functions, including in-memory functions and CLR scalar functions. Does not return information about table valued functions, and about scalar functions that are inlined with Scalar UDF Inlining.

SELECT 
    OBJECT_NAME(object_id) AS ScalarFunction, 
    execution_count,
    total_worker_time, 
    total_logical_reads, 
    total_elapsed_time,
    min_worker_time,  -- Note: microseconds
    max_worker_time,  -- Note: microseconds
    AvgWorkerTimePerExecution_microsecs = total_worker_time / (1. * execution_count)
FROM 
    sys.dm_exec_function_stats WITH(NOLOCK) 
WHERE 
    database_id = DB_ID()
    AND OBJECT_NAME(object_id) IS NOT NULL
ORDER BY 
    total_worker_time DESC 
OPTION(RECOMPILE);

Note: all time metrics are in microseconds

MS SQL DMVs for UDF performance stats - how to find top 10 worst UDFs

3 Answers3