Mahmoud ElMansy: knowledge meant to be free

SP That Consumes The Most CPU Resources And Most I/O Requests

This post will be about stored procedures and performance indicators .

If you want to show the SP that consumes the most CPU resources ?
you can run the following TSQL command:

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

To determine which SP has executed the most I/O requests ?
you can run the following TSQL code:

SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
,OBJECT_NAME(objectid,st.dbid) StoredProcedure
,MAX(cp.usecounts) execution_count
,SUM(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) total_IO
,SUM(qs.total_physical_reads + qs.total_logical_reads + qs.total_logical_writes) / (MAX(cp.usecounts)) avg_total_IO
,SUM(qs.total_physical_reads) total_physical_reads
,SUM(qs.total_physical_reads) / (MAX(cp.usecounts) * 1.0) avg_physical_read
,SUM(qs.total_logical_reads) total_logical_reads
,SUM(qs.total_logical_reads) / (MAX(cp.usecounts) * 1.0) avg_logical_read
,SUM(qs.total_logical_writes) total_logical_writes
,SUM(qs.total_logical_writes) / (MAX(cp.usecounts) * 1.0) avg_logical_writes
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
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_physical_reads + qs.total_logical_reads + qs.total_logical_writes) DESC

To determine which SP’s take the longest time to execute?
you can run the following TSQL code:

SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(objectid,st.dbid) SchemaName
,OBJECT_NAME(objectid,st.dbid) StoredProcedure
,MAX(cp.usecounts) execution_count
,SUM(qs.total_elapsed_time) total_elapsed_time
,SUM(qs.total_elapsed_time) / MAX(cp.usecounts) avg_elapsed_time
FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
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_elapsed_time) DESC

Notes:
First-> This query will apply from last restart or “DBCCFreeProcCache”
Second->This query the higher value does not means it has a problem you must consider execution count and time of execution and server resources availability