Skip to content

Instantly share code, notes, and snippets.

@BlitzErik

BlitzErik/gist:bf8bc5541a0b58675b92b73a076565e3 Secret

Created July 3, 2018 14:43
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save BlitzErik/bf8bc5541a0b58675b92b73a076565e3 to your computer and use it in GitHub Desktop.
Save BlitzErik/bf8bc5541a0b58675b92b73a076565e3 to your computer and use it in GitHub Desktop.
Download ZIP
This is bad and you should feel bad
Raw
gistfile1.txt
DECLARE @ScalarFunctions NVARCHAR(MAX) = N''
DECLARE @SortOrder NVARCHAR(MAX) = N'cpu'
SET @ScalarFunctions = N'
SELECT TOP 10
''Function: ''
+ QUOTENAME(COALESCE(OBJECT_SCHEMA_NAME(qs.object_id, qs.database_id),''''))
+ ''.''
+ QUOTENAME(COALESCE(OBJECT_NAME(qs.object_id, qs.database_id),'''')) AS QueryType,
COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), N''-- N/A --'') AS DatabaseName,
(total_worker_time / 1000.0) / execution_count AS AvgCPU,
(total_worker_time / 1000.0) AS TotalCPU,
CASE WHEN total_worker_time = 0 THEN 0
WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
ELSE CAST((total_worker_time / 1000.0) / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time)) AS MONEY)
END AS AverageCPUPerMinute,
CASE WHEN t.t_TotalWorker = 0 THEN 0
ELSE CAST(ROUND(100.00 * (total_worker_time / 1000.0) / t.t_TotalWorker, 2) AS MONEY)
END AS PercentCPUByType,
CASE WHEN t.t_TotalElapsed = 0 THEN 0
ELSE CAST(ROUND(100.00 * (total_elapsed_time / 1000.0) / t.t_TotalElapsed, 2) AS MONEY)
END AS PercentDurationByType,
CASE WHEN t.t_TotalReads = 0 THEN 0
ELSE CAST(ROUND(100.00 * total_logical_reads / t.t_TotalReads, 2) AS MONEY)
END AS PercentReadsByType,
CASE WHEN t.t_TotalExecs = 0 THEN 0
ELSE CAST(ROUND(100.00 * execution_count / t.t_TotalExecs, 2) AS MONEY)
END AS PercentExecutionsByType,
(total_elapsed_time / 1000.0) / execution_count AS AvgDuration,
(total_elapsed_time / 1000.0) AS TotalDuration,
total_logical_reads / execution_count AS AvgReads,
total_logical_reads AS TotalReads,
execution_count AS ExecutionCount,
CASE WHEN execution_count = 0 THEN 0
WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
ELSE CAST((1.00 * execution_count / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time))) AS money)
END AS ExecutionsPerMinute,
total_logical_writes AS TotalWrites,
total_logical_writes / execution_count AS AverageWrites,
CASE WHEN t.t_TotalWrites = 0 THEN 0
ELSE CAST(ROUND(100.00 * total_logical_writes / t.t_TotalWrites, 2) AS MONEY)
END AS PercentWritesByType,
CASE WHEN total_logical_writes = 0 THEN 0
WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
ELSE CAST((1.00 * total_logical_writes / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0)) AS money)
END AS WritesPerMinute,
qs.cached_time AS PlanCreationTime,
qs.last_execution_time AS LastExecutionTime,
NULL AS StatementStartOffset,
NULL AS StatementEndOffset,
NULL AS MinReturnedRows,
NULL AS MaxReturnedRows,
NULL AS AvgReturnedRows,
NULL AS TotalReturnedRows,
NULL AS LastReturnedRows,
NULL AS MinGrantKB,
NULL AS MaxGrantKB,
NULL AS MinUsedGrantKB,
NULL AS MaxUsedGrantKB,
NULL AS PercentMemoryGrantUsed,
NULL AS AvgMaxMemoryGrant,
NULL AS MinSpills,
NULL AS MaxSpills,
NULL AS TotalSpills,
NULL AS AvgSpills,
st.text AS QueryText,
query_plan AS QueryPlan,
t.t_TotalWorker,
t.t_TotalElapsed,
t.t_TotalReads,
t.t_TotalExecs,
t.t_TotalWrites,
qs.sql_handle AS SqlHandle,
qs.plan_handle AS PlanHandle,
NULL AS QueryHash,
NULL AS QueryPlanHash,
qs.min_worker_time / 1000.0,
qs.max_worker_time / 1000.0,
qs.min_elapsed_time / 1000.0,
qs.max_elapsed_time / 1000.0,
age_minutes,
age_minutes_lifetime,
' + QUOTENAME(@SortOrder, '''') + N'
FROM (SELECT TOP 10 x.*, xpa.*,
CAST((CASE WHEN DATEDIFF(mi, cached_time, GETDATE()) > 0 AND execution_count > 1
THEN DATEDIFF(mi, cached_time, GETDATE())
ELSE NULL END) as MONEY) as age_minutes,
CAST((CASE WHEN DATEDIFF(mi, cached_time, last_execution_time) > 0 AND execution_count > 1
THEN DATEDIFF(mi, cached_time, last_execution_time)
ELSE Null END) as MONEY) as age_minutes_lifetime
FROM sys.dm_exec_function_stats x
CROSS APPLY (SELECT * FROM sys.dm_exec_plan_attributes(x.plan_handle) AS ixpa
WHERE ixpa.attribute = ''dbid'') AS xpa
WHERE 1 = 1
AND COALESCE(DB_NAME(CAST(xpa.value AS INT)), '''') NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''32767'')
AND COALESCE(DB_NAME(CAST(xpa.value AS INT)), '''') NOT IN (SELECT name FROM sys.databases WHERE is_distributor = 1)
ORDER BY total_worker_time
) AS qs
CROSS JOIN(SELECT SUM(execution_count) AS t_TotalExecs,
SUM(CAST(total_elapsed_time AS BIGINT) / 1000.0) AS t_TotalElapsed,
SUM(CAST(total_worker_time AS BIGINT) / 1000.0) AS t_TotalWorker,
SUM(CAST(total_logical_reads AS BIGINT)) AS t_TotalReads,
SUM(CAST(total_logical_writes AS BIGINT)) AS t_TotalWrites
FROM sys.dm_exec_function_stats) AS t
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE 1 = 1
AND pa.attribute = ''dbid''
AND COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), '''') NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''32767'')
AND COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), '''') NOT IN (SELECT name FROM sys.databases WHERE is_distributor = 1)
ORDER BY total_worker_time
OPTION (RECOMPILE);
'
SELECT LEN(@ScalarFunctions) AS [@ScalarFunctionsLength];
--EXEC (@ScalarFunctions);
SET @ScalarFunctions = N'
SELECT TOP 10
''Function: ''
+ QUOTENAME(COALESCE(OBJECT_SCHEMA_NAME(qs.object_id, qs.database_id),''''))
+ ''.''
+ QUOTENAME(COALESCE(OBJECT_NAME(qs.object_id, qs.database_id),'''')) AS QueryType,
COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), N''-- N/A --'') AS DatabaseName,
(total_worker_time / 1000.0) / execution_count AS AvgCPU,
(total_worker_time / 1000.0) AS TotalCPU,
CASE WHEN total_worker_time = 0 THEN 0
WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
ELSE CAST((total_worker_time / 1000.0) / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time)) AS MONEY)
END AS AverageCPUPerMinute,
CASE WHEN t.t_TotalWorker = 0 THEN 0
ELSE CAST(ROUND(100.00 * (total_worker_time / 1000.0) / t.t_TotalWorker, 2) AS MONEY)
END AS PercentCPUByType,
CASE WHEN t.t_TotalElapsed = 0 THEN 0
ELSE CAST(ROUND(100.00 * (total_elapsed_time / 1000.0) / t.t_TotalElapsed, 2) AS MONEY)
END AS PercentDurationByType,
CASE WHEN t.t_TotalReads = 0 THEN 0
ELSE CAST(ROUND(100.00 * total_logical_reads / t.t_TotalReads, 2) AS MONEY)
END AS PercentReadsByType,
CASE WHEN t.t_TotalExecs = 0 THEN 0
ELSE CAST(ROUND(100.00 * execution_count / t.t_TotalExecs, 2) AS MONEY)
END AS PercentExecutionsByType,
(total_elapsed_time / 1000.0) / execution_count AS AvgDuration,
(total_elapsed_time / 1000.0) AS TotalDuration,
total_logical_reads / execution_count AS AvgReads,
total_logical_reads AS TotalReads,
execution_count AS ExecutionCount,
CASE WHEN execution_count = 0 THEN 0
WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
ELSE CAST((1.00 * execution_count / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time))) AS money)
END AS ExecutionsPerMinute,
total_logical_writes AS TotalWrites,
total_logical_writes / execution_count AS AverageWrites,
CASE WHEN t.t_TotalWrites = 0 THEN 0
ELSE CAST(ROUND(100.00 * total_logical_writes / t.t_TotalWrites, 2) AS MONEY)
END AS PercentWritesByType,
CASE WHEN total_logical_writes = 0 THEN 0
WHEN COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0) = 0 THEN 0
ELSE CAST((1.00 * total_logical_writes / COALESCE(age_minutes, DATEDIFF(mi, qs.cached_time, qs.last_execution_time), 0)) AS money)
END AS WritesPerMinute,
qs.cached_time AS PlanCreationTime,
qs.last_execution_time AS LastExecutionTime,
NULL AS StatementStartOffset,
NULL AS StatementEndOffset,
NULL AS MinReturnedRows,
NULL AS MaxReturnedRows,
NULL AS AvgReturnedRows,
NULL AS TotalReturnedRows,
NULL AS LastReturnedRows,
NULL AS MinGrantKB,
NULL AS MaxGrantKB,
NULL AS MinUsedGrantKB,
NULL AS MaxUsedGrantKB,
NULL AS PercentMemoryGrantUsed,
NULL AS AvgMaxMemoryGrant,
NULL AS MinSpills,
NULL AS MaxSpills,
NULL AS TotalSpills,
NULL AS AvgSpills,
st.text AS QueryText,
query_plan AS QueryPlan,
t.t_TotalWorker,
t.t_TotalElapsed,
t.t_TotalReads,
t.t_TotalExecs,
t.t_TotalWrites,
qs.sql_handle AS SqlHandle,
qs.plan_handle AS PlanHandle,
NULL AS QueryHash,
NULL AS QueryPlanHash,
qs.min_worker_time / 1000.0,
qs.max_worker_time / 1000.0,
qs.min_elapsed_time / 1000.0,
qs.max_elapsed_time / 1000.0,
age_minutes,
age_minutes_lifetime,
' + QUOTENAME(@SortOrder, '''')
IF 1 = 1
BEGIN
SET @ScalarFunctions += N'
FROM (SELECT TOP 10 x.*, xpa.*,
CAST((CASE WHEN DATEDIFF(mi, cached_time, GETDATE()) > 0 AND execution_count > 1
THEN DATEDIFF(mi, cached_time, GETDATE())
ELSE NULL END) as MONEY) as age_minutes,
CAST((CASE WHEN DATEDIFF(mi, cached_time, last_execution_time) > 0 AND execution_count > 1
THEN DATEDIFF(mi, cached_time, last_execution_time)
ELSE Null END) as MONEY) as age_minutes_lifetime
FROM sys.dm_exec_function_stats x
CROSS APPLY (SELECT * FROM sys.dm_exec_plan_attributes(x.plan_handle) AS ixpa
WHERE ixpa.attribute = ''dbid'') AS xpa
WHERE 1 = 1
AND COALESCE(DB_NAME(CAST(xpa.value AS INT)), '''') NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''32767'')
AND COALESCE(DB_NAME(CAST(xpa.value AS INT)), '''') NOT IN (SELECT name FROM sys.databases WHERE is_distributor = 1)
ORDER BY total_worker_time
) AS qs
CROSS JOIN(SELECT SUM(execution_count) AS t_TotalExecs,
SUM(CAST(total_elapsed_time AS BIGINT) / 1000.0) AS t_TotalElapsed,
SUM(CAST(total_worker_time AS BIGINT) / 1000.0) AS t_TotalWorker,
SUM(CAST(total_logical_reads AS BIGINT)) AS t_TotalReads,
SUM(CAST(total_logical_writes AS BIGINT)) AS t_TotalWrites
FROM sys.dm_exec_function_stats) AS t
CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE 1 = 1
AND pa.attribute = ''dbid''
AND COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), '''') NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''32767'')
AND COALESCE(DB_NAME(database_id), CAST(pa.value AS sysname), '''') NOT IN (SELECT name FROM sys.databases WHERE is_distributor = 1)
ORDER BY total_worker_time
OPTION (RECOMPILE);
'
END
SELECT LEN(@ScalarFunctions) AS [@ScalarFunctionsLength];
--EXEC (@ScalarFunctions);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment