asked
How to manipulate the sys.dm_exec_Procedure_Stats?
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 ![alt text][1] 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? [1]: /storage/temp/3487-proc-stats.png
have a look at [link text][1] As you can see, most columns are microseconds, so divide by 1.000.000 to get it in seconds a read is an 8K page, so your calculation is correct [1]:
https://msdn.microsoft.com/en-us/library/cc280701.aspx
18 People are following this question.
