Checking the Stored Procedures

Checking the Stored Procedures
Execution details for the Stored Procedure

    SELECT CASE WHEN database_id = 32767
       then 'Resource'
       ELSE
     DB_NAME(database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,*  FROM sys.dm_exec_procedure_stats
      where OBJECT_SCHEMA_NAME(object_id,database_id) !='sys'
      and OBJECT_SCHEMA_NAME(object_id,database_id) !='dbo'
      order by last_execution_time desc

TOP 100 SQLs within a specified time

SELECT TOP 100

DB_NAME(st.dbid) AS database_name

,       st.objectid AS OBJECT_ID

, creation_time

,       last_execution_time

,       total_logical_reads AS [LogicalReads]

,       total_logical_writes AS [LogicalWrites]

,       execution_count

,             max_elapsed_time/ 1000000.00  AS MaxElapsedTimems

,             ISNULL(total_elapsed_time / 1000000.00 / NULLIF(execution_count, 0), 0) AS AvgElapsedTimesecs

,       total_logical_reads+total_logical_writes AS [AggIO]

,       (total_logical_reads+total_logical_writes)/(execution_count+0.0) AS [AvgIO]

,      st.TEXT

FROM sys.dm_exec_query_stats  qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

WHERE total_logical_reads+total_logical_writes > 0

AND sql_handle IS NOT NULL AND last_execution_time > '2015-03-02 06:20:37'

ORDER BY [AvgElapsedTimesecs] DESC

credits : https://www.simple-talk.com/sql/performance/which-of-your-stored-procedures-are-using-the-most-resources/