sql server 查看存储过程执行时间

        查看平均执行消耗时间最长的30个存储过程

SELECT TOP 30 a.object_id, a.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
a.cached_time, a.last_execution_time, a.total_elapsed_time, a.total_elapsed_time/a.execution_count AS [avg_elapsed_time]
FROM sys.dm_exec_procedure_stats AS a
ORDER BY [avg_elapsed_time] DESC;
GO

按照执行时间排序的前10 的存储过程的执行信息

SELECT TOP 10 a.object_id, a.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
a.cached_time, a.last_execution_time, a.total_elapsed_time, a.total_elapsed_time/a.execution_count AS [avg_elapsed_time],
a.execution_count,
a.total_physical_reads/a.execution_count avg_physical_reads,
a.total_logical_writes,
a.total_logical_writes/ a.execution_count avg_logical_reads,
a.last_elapsed_time,
a.total_elapsed_time / a.execution_count  avg_elapsed_time,
b.text,c.query_plan
FROM sys.dm_exec_procedure_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c
ORDER BY [total_worker_time] DESC;
GO