sql server - how to get all stored procedure execution time , cup time etc -

sql server - how to get all stored procedure execution time , cup time etc -

select db_name(st.dbid) dbname ,object_schema_name(objectid,st.dbid) schemaname ,object_name(objectid,st.dbid) storedprocedure ,max(cp.usecounts) execution_count ,sum(qs.total_elapsed_time) total_elapsed_time ,sum(qs.total_elapsed_time) / max(cp.usecounts) avg_elapsed_time sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle) st bring together sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle cp.objtype = 'proc' grouping db_name(st.dbid),object_schema_name(objectid,st.dbid), object_name(objectid,st.dbid) order sum(qs.total_elapsed_time) desc

i running above query not able find sp list details created in database. shows fewer sp.

please help me. waiting valuable reply.

you seek this, lot more complicated gets around fact not every stored procedure have statistics:

create table #x( database_id int, databasename sysname, schemaname sysname, procedurename sysname, [object_id] int); declare @sql nvarchar(max) = ''; select @sql = @sql + n'insert #x select ' + convert(varchar(50), d.database_id) + ', ''' + name + ''', s.name, p.name, p.[object_id] ' + quotename(d.name) + '.sys.schemas s inner bring together ' + quotename(d.name) + '.sys.procedures p on p.schema_id = s.schema_id;' sys.databases d d.database_id > 4; exec sp_executesql @sql; plandata ( select st.[dbid] database_id, st.objectid [object_id], db_name(st.[dbid]) dbname, object_schema_name(st.objectid, st.[dbid]) schemaname, object_name(st.objectid, st.[dbid]) storedprocedure, max(cp.usecounts) execution_count, sum(qs.total_elapsed_time) total_elapsed_time, sum(qs.total_elapsed_time) / max(cp.usecounts) avg_elapsed_time sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.plan_handle) st left bring together sys.dm_exec_cached_plans cp on cp.plan_handle = qs.plan_handle cp.objtype = 'proc' grouping st.[dbid], st.objectid, db_name(st.[dbid]), object_schema_name(st.objectid, st.[dbid]), object_name(st.objectid, st.[dbid])) select x.databasename, x.schemaname, x.procedurename, pd.execution_count, pd.total_elapsed_time, pd.avg_elapsed_time #x x left bring together plandata pd on pd.database_id = x.database_id , pd.[object_id] = x.[object_id]; drop table #x;

from msdn regarding sys.dm_exec_query_stats, "the view contains 1 row per query statement within cached plan, , lifetime of rows tied plan itself. when plan removed cache, corresponding rows eliminated view."

sql-server database stored-procedures database-performance