I executed the below query and obtained the result set which is shown in the image.
select top 1 st.dbid,st.objectid, ps.execution_count, total_Worker_Time, total_physical_reads, total_elapsed_time, total_logical_reads, total_logical_writes from sys.dm_exec_procedure_stats ps cross apply sys.dm_exec_sql_text(ps.plan_handle) st order by total_logical_reads + total_logical_writes desc
My intention is to find the execution statistics of a Stored Procedure with top IO.
From the SP's statistics obtained, I have derived the below values.
Total CPU Time = 27237.03 secs <=(Total_Worker_Time/1000000)
Avg CPU Time = 405.12 Millisecs <=((Total_Worker_Time/execution_count)/1000)
Total Elapsed Time = 27619.94 secs <=(Total_Elapsed_Time/1000000)
Avg Elapsed Time = 410.81 Millisecs <=((Total_Elapsed_Time/execution_count)/1000)
Total Logical IO = 16912.25 GB <=((Total_Logical_Reads + Total_Logical_Writes)* 8/1024/1024)
Avg Logical IO = 257.58 MB <=(((Total_Logical_Reads + Total_Logical_Writes)/execution_count)* 8/1024/1024)
Whether the above manipulated approach is correct?
asked Jun 14, 2016 at 07:35 PM in Default