/* STORED PROCEDURES AND FUNCTIONS EXECUTION TIME, COUNT AND AVERAGE */ SELECT DB_NAME(st.dbid) DBName ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName ,OBJECT_NAME(st.objectid,dbid) StoredProcedure ,max(cp.usecounts) Execution_count ,sum(qs.total_worker_time) total_cpu_time ,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0) avg_cpu_time FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st where DB_NAME(st.dbid) is not null and cp.objtype = 'proc' group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid) order by sum(qs.total_worker_time) desc;
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter