/* Best practice for SQL Server data manipulation is to use stored procedures, this ensures consistence data changes and encapsulated data layer from data access / business layer. This Transact-SQL script gives an overview of all stored procedures and their usage / execution statistics. Works with SQL Server 2008 and higher versions in all editions. Requires VIEW SERVER STATE permission. Link: sys.dm_exec_procedure_stats: http://msdn.microsoft.com/en-us/library/cc280701.aspx */
-- Stored Procedure Execution Statistics SELECT ISNULL(DBS.name, '') AS DatabaseName , OBJECT_NAME(EPS.object_id, EPS.database_id) AS ObjectName , EPS.cached_time AS CachedTime , EPS.last_elapsed_time AS LastElapsedTime , EPS.execution_count AS ExecutionCount , EPS.total_worker_time / EPS.execution_count AS AvgWorkerTime , EPS.total_elapsed_time / EPS.execution_count AS AvgElapsedTime , (EPS.total_logical_reads + EPS.total_logical_writes) / EPS.execution_count AS AvgLogicalIO FROM sys.dm_exec_procedure_stats AS EPS LEFT JOIN sys.databases AS DBS ON EPS.database_id = DBS.database_id ORDER BY AvgWorkerTime DESC;
