10

In order to diagnose some performance issues I would like to get a better understanding of the number of times certain procedures are called compared to the system performance. Is there a way to get the number of times each procedure has been called during a certain timespan?

17

You can get this (and more) from Dynamic Management Views (DMVs). To get statistics for a particular stored procedure, try the following query.

SELECT
    OBJECT_NAME(qt.objectid)
  , qs.execution_count AS [Execution Count]
  , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
  , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
  , qs.total_worker_time AS [TotalWorkerTime]
  , qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
  , qs.max_logical_reads
  , qs.max_logical_writes
  , qs.total_physical_reads
  , DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    qt.[dbid] = DB_ID()
AND qt.objectid = OBJECT_ID('StoredProcedureName')
OPTION (RECOMPILE);

To look at the most frequently executed procedures:

SELECT
    OBJECT_NAME(qt.objectid)
  , qs.execution_count AS [Execution Count]
  , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
  , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
  , qs.total_worker_time AS [TotalWorkerTime]
  , qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
  , qs.max_logical_reads
  , qs.max_logical_writes
  , qs.total_physical_reads
  , DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    qt.[dbid] = DB_ID()
ORDER BY
    qs.execution_count DESC
OPTION (RECOMPILE);

The values reported are cumulative since the last restart. If you want to measure over a fixed period, use the command below to reset wait stats.

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

If you wanted to measure fixed timespans across the day, you could feed the query output to a table via an agent job and either a) calculate the values between two runs or b) issue the wait stats reset as the last step in the agent job.

Alternatively, capture a profiler trace and run it through Clear Trace.

  • It seems I am getting a many to many result set but not exactly. For example, I see under the column object_name response several of the same objects but most of the details are the same with a few exceptions. Columns details that match: ExecutionCount, Call/Second, AgeInCache. Column details that do not match: AvgWorkerTime, TotalWorkerTime, AvgElapesedTime. What would cause the many to many result? – kstubs Oct 3 '18 at 22:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.