Script to find out Stored Procedures which are Using most resources

While doing the stored procedure performance tuning, you can use sys.dm_exec_procedure_stats DMV to get resource intensive procedures.

You can use the below script for it.
 
SELECT DB_NAME(database_id) AS DatabaseName
      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
      ,total_worker_time / execution_count AS Average_CPU
      ,total_elapsed_time / execution_count AS Average_Elapsed_Time
      ,total_logical_reads / execution_count AS Average_Logical_Reads
      ,total_logical_writes / execution_count AS Average_Logical_Writes
      ,total_physical_reads  / execution_count AS Average_Physical_Reads
FROM sys.dm_exec_procedure_stats
where database_id <> 32767
ORDER BY Average_Logical_Reads DESC