SQL- Scalar Functions (UDFs)

PerformanceStats.sql

; -- Previous statement must be properly terminated
WITH detailPerPlan AS
(
    SELECT
        defs.database_id,
        defs.object_id,
        defs.total_worker_time,
        defs.execution_count,
        defs.total_elapsed_time,
        defs.total_elapsed_time / defs.execution_count AS avg_elapsed_time,
        defs.last_elapsed_time,
        defs.last_execution_time,
        defs.cached_time
        , ca.cachedSeconds
        , ca2.total_worker_time_s
        , ca2.total_elapsed_time_s
        , ca2.total_worker_time_s / ca.cachedSeconds AS WorkerTimeSecPerSecondsCached
        , ca2.total_elapsed_time_s / ca.cachedSeconds AS ElapsedTimeSecPerSecondsCached
        , defs.execution_count / ca.cachedSeconds AS ExecutionsPerSecondsCached
    FROM sys.dm_exec_function_stats AS defs WITH (NOLOCK)
    CROSS APPLY 
    (
        VALUES (CAST(DATEDIFF(SECOND, defs.cached_time, GETDATE()) AS decimal(15,5)))
    ) AS ca(cachedSeconds)
    CROSS APPLY 
    (
        VALUES 
        (
            defs.total_worker_time / POWER(10.,6) 
            , defs.total_elapsed_time / POWER(10.,6) 
        )
    ) AS ca2 (total_worker_time_s, total_elapsed_time_s)
)
, groupedDatabaseObject
AS
(
    SELECT 
        dpp.database_id
        , dpp.object_id
        , SUM(dpp.execution_count) AS execution_count_sum
        , SUM(dpp.total_worker_time) AS total_worker_time_sum
        , CAST(SUM(dpp.total_worker_time) / ((SUM(dpp.execution_count)) * 1.) AS decimal(20,2)) AS avg_worker_time_sum
        , SUM(dpp.total_elapsed_time) AS total_elapsed_time_sum
        , CAST(SUM(dpp.total_elapsed_time) / ((SUM(dpp.execution_count)) * 1.) AS decimal(20,2)) AS avg_elapsed_time_sum
        , CAST(SUM(dpp.total_worker_time_s) / SUM (dpp.cachedSeconds) AS decimal(20,2)) AS WorkerTimeSecPerSecondsCached_Sum
        , CAST(SUM(dpp.total_elapsed_time_s) / SUM (dpp.cachedSeconds) AS decimal(20,2)) AS ElapsedTimeSecPerSecondsCached_Sum
        , CAST(SUM(dpp.execution_count) / SUM (dpp.cachedSeconds) AS decimal(20,2)) AS ExecutionsPerSecondsCached_Sum
    FROM detailPerPlan AS dpp
    GROUP BY 
        dpp.database_id
        , dpp.object_id
)
SELECT 
    CASE gdo.database_id
        WHEN 32767 /* https://docs.microsoft.com/en-us/sql/relational-databases/databases/resource-database?view=sql-server-ver16 */
        THEN N'Resource database (Hidden)'
    ELSE 
        DB_NAME (gdo.database_id)
    END AS [Database Name],
    CASE gdo.database_id
        WHEN 32767
        THEN OBJECT_NAME (gdo.object_id)
    ELSE
        OBJECT_NAME (gdo.object_id, gdo.database_id) 
    END AS [Function Name]
    , gdo.*
FROM groupedDatabaseObject AS gdo
ORDER BY WorkerTimeSecPerSecondsCached_Sum DESC

References.sql

DROP TABLE IF EXISTS #UdfReferences
CREATE TABLE #UdfReferences
(
    dbName         nvarchar(128),
    refingObjId    int,
    refingObjType  nvarchar(60),
    refingSchName  nvarchar(128),
    refingObjName  nvarchar(128),
    refingColName  nvarchar(128),
    fnObjId        int,
    fnSchName      nvarchar(128),
    fnName         nvarchar(128),
    is_inlineable  bit,
    inliningStatus varchar(3)
)

INSERT INTO #UdfReferences
(
    dbName,
    refingObjId,
    refingObjType,
    refingSchName,
    refingObjName,
    refingColName,
    fnObjId,
    fnSchName,
    fnName,
    is_inlineable,
    inliningStatus
)
SELECT
    DB_NAME() AS dbName
    , ro.object_id AS refingObjId
    , ro.type_desc AS refingObjType
    , SCHEMA_NAME(ro.schema_id) AS refingSchName
    , CONCAT
    (
        OBJECT_NAME(ro.parent_object_id) + N'_'
        , ro.name
    ) AS refingObjName /* if constraint, CONCAT with the parent table name */
    , COALESCE(cc.name, chkConstraint.colName, dfConstraint.colName) AS refingColName
    , fno.object_id AS fnObjId
    , fns.name AS fnSchName
    , fno.name AS fnName
    , sm.is_inlineable
    , IIF(sm.inline_type = 0, 'OFF', 'ON') AS inliningStatus
FROM sys.sql_expression_dependencies AS sed
JOIN sys.objects AS ro
    ON ro.object_id = sed.referencing_id
LEFT JOIN
(
    SELECT
        cc.object_id
        , c.name AS colName
    FROM sys.check_constraints AS cc
    JOIN sys.columns AS c
        ON cc.parent_object_id = c.object_id
        AND cc.parent_column_id = c.column_id
) chkConstraint
    ON chkConstraint.object_id = ro.object_id
LEFT JOIN
(
    SELECT
        dc.object_id
        , c.name AS colName
    FROM sys.default_constraints AS dc
    JOIN sys.columns AS c
        ON dc.parent_object_id = c.object_id
        AND dc.parent_column_id = c.column_id
) dfConstraint
    ON dfConstraint.object_id = ro.object_id
LEFT JOIN sys.columns AS cc /* computed column */
    ON sed.referencing_id = cc.object_id
    AND sed.referencing_minor_id = cc.column_id
JOIN sys.objects AS fno
    ON fno.name = sed.referenced_entity_name
    AND fno.type = 'FN'
JOIN sys.schemas AS fns
    ON fns.schema_id = fno.schema_id
    AND fns.name = sed.referenced_schema_name
JOIN sys.sql_modules AS sm
    ON sm.object_id = fno.object_id

SELECT
    *
FROM #UdfReferences AS ur
WHERE
    ur.refingColName IS NOT NULL
    OR ur.refingObjType IN
    (
        N'VIEW'
        , N'SQL_TRIGGER'
    )
ORDER BY ur.refingObjType

SELECT
    ur.fnObjId
    , ur.fnSchName
    , ur.fnName
    , ur.is_inlineable
    , ur.inliningStatus
    , COUNT(1) AS objRefCount
    , STRING_AGG
    (
        CONCAT(CAST(N'' AS nvarchar(MAX)), ur.refingSchName,N'.',ur.refingObjName)
        , CHAR(13) + CHAR(10)
    ) AS aggregatedReferencingObjects
FROM #UdfReferences AS ur
GROUP BY
    ur.fnObjId
    , ur.fnSchName
    , ur.fnName
    , ur.is_inlineable
    , ur.inliningStatus
ORDER BY objRefCount DESC