x

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

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?

proc-stats.png (7.7 kB)
more ▼

asked Jun 14, 2016 at 07:35 PM in Default

avatar image

kpsenthilkumar86
31 2 3 8

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

have a look at link text 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

more ▼

answered Jun 28, 2016 at 07:41 PM

avatar image

Wilfred van Dijk
3.6k 29 39 49

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x75
x12
x5

asked: Jun 14, 2016 at 07:35 PM

Seen: 117 times

Last Updated: Jun 28, 2016 at 07:41 PM

Copyright 2018 Redgate Software. Privacy Policy