-3

I'm using a SQL Server database, we have 50-80 stored procedures which are being used by a .NET application. But the application is very slow and returning the results with huge delay now a days, so I would like to identify the slow running stored procedure in the database.

Thanks in advance.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Raja Rao
  • 7
  • 3
  • Have you considered a log table in which you record the procedure name and the start and end time. Finding the average longest executing procedures then becomes trivial. You can also look at Query Store to identify the longest running queries, among other metrics. – Stu Aug 15 '21 at 17:56

1 Answers1

1

Have you looked at sys.dm_exec_procedure_stats? You should at least get started with something like this:

select
    isnull(object_name(s.object_id, s.database_id), convert(varchar, object_id)) as name,
    s.total_logical_reads,
    s.total_worker_time,
    s.total_elapsed_time,
    s.execution_count,
    cached_time,
    last_execution_time
from
    sys.dm_exec_procedure_stats s
order by 
    total_worker_time desc
option (recompile)
James Z
  • 12,209
  • 10
  • 24
  • 44
  • It's not perfect because plans get dropped and re-created, so the statistics won't stay, but you should at least get some idea. – James Z Aug 15 '21 at 18:13
  • One can insert the results into a permanent table periodically, with an additional column like collection_time. That will allow comparison over time. – Dan Guzman Aug 15 '21 at 21:12