Thursday, August 22, 2013

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

SELECT ss.sum_execution_count
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
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.

No comments:

Post a Comment