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 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




ċ
Andy Hughes,
Jun 7, 2020, 9:52 AM
Comments