Tuesday, July 03, 2012

DMV - SPROC with the highest average CPU

This handy bit of code shows the stored procedures with the highest average CPU time in SQL Server..

SELECT TOP 50 * FROM
(SELECT OBJECT_NAME(s2.objectid) AS ProcName,
SUM(s1.total_worker_time/s1.execution_count) AS AverageCPUTime,s2.objectid,
SUM(execution_count) AS execution_count
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
GROUP BY OBJECT_NAME(s2.objectid),objectid) x
WHERE OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
AND EXISTS (SELECT 1 FROM sys.procedures s
WHERE s.is_ms_shipped = 0
AND s.name = x.ProcName )
ORDER BY AverageCPUTime DESC



No comments: