Advertisement
etsuskier

Procedure Stats - Calls Per Minute

Apr 9th, 2018
85
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.67 KB | None | 0 0
  1.  
  2. USE [master]
  3.  
  4. SELECT TOP 50
  5.        DB_NAME(ps.database_id) AS database_name,
  6.        OBJECT_NAME(ps.object_id, ps.database_id) AS procedure_name,
  7.        ps.cached_time,
  8.        ps.execution_count,
  9.        ps.execution_count / DATEDIFF(MINUTE, ps.cached_time, GETDATE()) AS calls_per_minute,
  10.        ps.total_worker_time / 1000 AS total_worker_time_ms,
  11.        ps.total_worker_time / ps.execution_count / 1000 AS avg_worker_time_ms,
  12.        ps.min_worker_time / 1000 AS min_worker_time_ms,
  13.        ps.max_worker_time / 1000 AS max_worker_time_ms,
  14.        ps.last_worker_time / 1000 AS last_worker_time_ms,
  15.        ps.total_elapsed_time / 1000 AS total_elapsed_time_ms,
  16.        ps.total_elapsed_time / ps.execution_count / 1000 AS avg_elapsed_time_ms,
  17.        ps.min_elapsed_time / 1000 AS min_elapsed_time_ms,
  18.        ps.max_elapsed_time / 1000 AS max_elapsed_time_ms,
  19.        ps.last_elapsed_time / 1000 AS last_elapsed_time_ms,
  20.        ps.total_logical_reads,
  21.        ps.total_logical_reads / ps.execution_count AS avg_logical_reads,
  22.        ps.min_logical_reads,
  23.        ps.max_logical_reads,
  24.        ps.last_logical_reads,
  25.        ps.total_physical_reads,
  26.        ps.total_physical_reads / ps.execution_count AS avg_physical_reads,
  27.        ps.min_physical_reads,
  28.        ps.max_physical_reads,
  29.        ps.last_physical_reads,
  30.        ps.total_logical_writes,
  31.        ps.total_logical_writes / ps.execution_count AS avg_logical_writes,
  32.        ps.min_logical_writes,
  33.        ps.max_logical_writes,
  34.        ps.last_logical_writes
  35.   FROM sys.dm_exec_procedure_stats AS ps
  36.  --WHERE OBJECT_NAME(ps.object_id, ps.database_id) LIKE 'XXXXXXXXXXXXXXX%'
  37.  ORDER BY calls_per_minute DESC
  38. OPTION (RECOMPILE)
  39. GO
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement