CODEWITHSUNDEEP
sqlsql-server-2008t-sqlsqlperformance
Simon
Simon

Reputation: 1476

sys.dm_exec_procedure_stats - am I interpreting it correctly

Can anybody confim if [Total Duration (s)] column of the query below that use the dm_exec_procedure_stats are giving me the average time it takes for a query to return its results? I'm concerned that this is only one element of the time it takes these queries to retuirn the results as they seem sligtly low.

Thanks to everyone who takes the time to look.

Any help or advise would be appreciated

SELECT SUBSTRING(qt.TEXT, CHARINDEX('USP_', qt.TEXT, 1), CHARINDEX(']', qt.TEXT, CHARINDEX('USP_', qt.TEXT, 1)) - CHARINDEX('USP_', qt.TEXT, 1)) AS Procedure_Name,
       qt.TEXT,
       CAST(total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))                                                                                    AS [Total Duration (s)],
       CASE
         WHEN execution_count = 0 THEN 0
         ELSE CAST(total_worker_time * 100.0 / total_elapsed_time AS DECIMAL(28, 2))
       END                                                                                                                                       AS [% CPU],
       CASE
         WHEN execution_count = 0 THEN 0
         ELSE CAST(( total_elapsed_time - total_worker_time ) * 100.0 / total_elapsed_time AS DECIMAL(28, 2))
       END                                                                                                                                       AS [% Waiting],
       execution_count,
       CASE
         WHEN execution_count = 0 THEN 0
         ELSE CAST(total_elapsed_time / 1000000.0 / execution_count AS DECIMAL(28, 2))
       END                                                                                                                                       AS [Average Duration (s)],
       sql_handle,
       plan_handle,
       object_id,
       total_elapsed_time,
       total_worker_time,
       execution_count,
       total_physical_reads,
       total_logical_reads,
       total_logical_writes
FROM   sys.dm_exec_procedure_stats qs
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE  qt.text LIKE '%USP%'

ORDER  BY 1

Upvotes: 2

Views: 2125

Answers (1)

user275683
user275683

Reputation:

to get average time you need to divideTOTAL / COUNT then divide by 1000000.0 to get seconds.

here is the code:

,CONVERT(DECIMAL(26,10),(qs.total_elapsed_time / qs.execution_count)) / 1000000.0 AS [avg_elapsed_time_inSec]

Upvotes: 3

Related Questions