Query to find total number of logical reads/physical reads done, when a given stored procedure is executed

SELECT ss.sum_execution_count

,t.TEXT

,ss.sum_total_elapsed_time

,ss.sum_total_worker_time

,ss.sum_total_logical_reads

,ss.sum_total_logical_writes

FROM (SELECT s.plan_handle

,SUM(s.execution_count) sum_execution_count

,SUM(s.total_elapsed_time) sum_total_elapsed_time

,SUM(s.total_worker_time) sum_total_worker_time

,SUM(s.total_logical_reads) sum_total_logical_reads

,SUM(s.total_logical_writes) sum_total_logical_writes

FROM sys.dm_exec_query_stats s

GROUP BY s.plan_handle

) AS ss

CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t

WHERE t.TEXT LIKE '%PROCEDURE NAME HEREt%'

ORDER BY sum_total_logical_reads DESC



If you want to collect this data for a specific execution, you need to save the data before execution into a table, and then after execution read the DMV again to compute the delta. A presumptions is that there are no other executions of the procedure at the same time.

Rather than using dm.sys_exec_query_stats, you can use dm.sys_exec_procedure_stats, so that you get values on procedure level instead rather than on query level.