SQL Stored Procedures Performance

 Execute below to find performance of SQL Stored Procedures. Review result with development team as there might be missing "SET NOCOUNT ON" missing.

DECLARE @varSQL_Start_Time DATETIME, @varPresent_Time DATETIME, @varMins DECIMAL(20,2)

SELECT @varSQL_Start_Time = sqlserver_start_time,

@varPresent_Time = GETDATE()

FROM sys.dm_os_sys_info 

SELECT @varMins = DATEDIFF(mi, @varSQL_Start_Time, @varPresent_Time)

select top 25 

DB_NAME(database_id) AS [Database_Name],

OBJECT_NAME([object_id])  AS [Stored_Procedure_Name],

FORMAT([Execution_Count], 'N0') as [Execution_Count],

CAST(CAST(execution_count AS BIGINT)/@varMins AS DECIMAL(20,0)) AS [Executions_per_Minute],

CAST(CAST(execution_count AS BIGINT)/(@varMins*60) AS DECIMAL(20,0)) AS [Executions_per_Second],

FORMAT(CAST(total_worker_time/CAST(execution_count AS DECIMAL(20,2)) AS DECIMAL(20,0)), 'N0') AS [AVG_Worker_Time],

FORMAT(CAST(tOTAL_elapsed_time/CAST(execution_count AS DECIMAL(20,2)) AS DECIMAL(20,0)), 'N0')  AS [AVG_Execution_Time],

FORMAT(CAST(total_logical_reads/CAST(execution_count AS DECIMAL(20,2)) AS DECIMAL(20,0)), 'N0')  AS [AVG_Reads],

FORMAT(CAST(total_logical_writes/CAST(execution_count AS DECIMAL(20,2)) AS DECIMAL(20,0)), 'N0')  AS [AVG_Writes]

from sys.dm_exec_procedure_stats

WHERE database_id = DB_ID()

order by CAST(execution_count AS BIGINT) desc --Execution_Count

--order by CAST(total_worker_time/CAST(execution_count AS DECIMAL(20,2)) AS DECIMAL(20,0)) desc --Worker Time

--order by CAST(tOTAL_elapsed_time/CAST(execution_count AS DECIMAL(20,2)) AS DECIMAL(20,0)) desc -- Execution Time

--order by CAST(total_logical_reads/CAST(execution_count AS DECIMAL(20,2)) AS DECIMAL(20,0)) desc --Reads

--order by CAST(total_logical_writes/CAST(execution_count AS DECIMAL(20,2)) AS DECIMAL(20,0)) desc --Writs