1

I'm trying to analyze 1000+ stored procedures and I need to find out the:

  • Elapsed Time in Sec (AVG based on # of iterations)
  • CPU (AVG based on # of iterations)
  • Logical Reads

At first I'm using SQL Query Stress, but it will take me so much time to test-run each stored procedure and analyzing test parameters.

So my question is, is there an easier way to get what I needed?

1

If these are in a live database, then you can use the DMV sys.dm_exec_procedure_stats to pull information for all the Stored Procedures that have been run and cached on the instance.

A basic query to pull all procedures ordered by the Average Duration may look something like this:

USE <DATABASE>;
GO

SELECT  
  o.name, 
  deps.total_elapsed_time/CAST(deps.execution_count AS DECIMAL(10,2))/1000000 AS AvgDurationInS,
  deps.total_worker_time/CAST(deps.execution_count AS DECIMAL(10,2))/1000000 AS AvgCPUInS,
  deps.total_logical_reads/CAST(deps.execution_count AS DECIMAL(10,2)) AS AvgLogicalReads,
  deps.cached_time
FROM sys.dm_exec_procedure_stats AS deps
INNER JOIN sys.objects AS o
  ON o.object_id = deps.object_id
WHERE deps.database_id = DB_ID()
ORDER BY AvgDurationInS DESC;

However, this doesn't show you all the queries within the Stored Procedure and break down each one by the same metrics. You can do that using sys.dm_exec_query_stats

  • Does the sys.dm_exec_procedure_stats only store information of stored procedures that has ran and had been cached already? Because there are some stored procedures that aren't appearing when I run the query you gave. – kganciro Mar 14 '16 at 7:20
  • Yes, it only stores historic information about procedures that have run – Mark Sinkinson Mar 14 '16 at 7:22

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.