About Me

Barking, Essex, United Kingdom
MCITP DBA ,MCITP BI & Oracle OCP 11G

Tuesday, June 2, 2015

Find SP Execution in seconds

SELECT  d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
    d.cached_time, d.last_execution_time,
       --d.total_elapsed_time,
       CAST((d.total_elapsed_time) / 1000000.0 AS DECIMAL(28,2)) as TotalElapsedtime_Seconds,
    --d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
       CAST((d.total_elapsed_time) / 1000000.0 AS DECIMAL(28,2))/d.execution_count as [avg_elapsed_time_seconds],
   CAST((d.last_elapsed_time) / 1000000.0 AS DECIMAL(28,2)) as [last_elapsed_time_Seconds] ,
    d.execution_count
FROM sys.dm_exec_procedure_stats AS d
where last_execution_time >='2015-06-02 08:00:21.527'
and d.database_ID = 5
ORDER BY [total_worker_time] DESC;


No comments:

Post a Comment