question

kpsenthilkumar86 avatar image
kpsenthilkumar86 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
performance-tuningperformance-metricsio
proc-stats.png (7.5 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.