Get SP Usage since last reboot

Applicability:

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

Credits:

Author:Phil Factor/ChillyDBA

Date: 5 Jun 2020

Description

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

Code

-- get execution counts and reads/writes totals for all SPs and XPs SELECTDB_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_timeFROM sys.dm_exec_procedure_statsORDER BYexecution_count * max_physical_reads DESC