SQL Server - Analyzing Multiple Stored Procedure Statistics

Question

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?

Answer 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

• kganciro: 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.
• Mark Sinkinson: Yes, it only stores historic information about procedures that have run