Last active
October 2, 2022 19:50
-
-
Save zikato/c9bddcb0fecc174b7e93398bbcd64221 to your computer and use it in GitHub Desktop.
SQL- Scalar Functions (UDFs)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
; -- 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment