Get SP Usage since last reboot

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Author:Phil Factor/ChillyDBA

Date: 5 Jun 2020

Get details of SP and XP usage since the last SQL Server restart.

-- get execution counts and reads/writes totals for all SPs and XPs

SELECT

DB_NAME(database_id) AS DatabaseName

,OBJECT_NAME(object_id, database_id) AS ObjectName

,type_desc AS ObjectType

,execution_count * max_physical_reads AS combinedreads

,last_execution_time

,execution_count

,total_physical_reads

,last_physical_reads

,min_physical_reads

,max_physical_reads

,total_logical_writes

,last_logical_writes

,min_logical_writes

,max_logical_writes

,total_logical_reads

,last_logical_reads

,min_logical_reads

,max_logical_reads

,total_elapsed_time

,last_elapsed_time

,min_elapsed_time

,max_elapsed_time

FROM sys.dm_exec_procedure_stats

ORDER BY

execution_count * max_physical_reads DESC