dm_exec_procedure_stats does it give query times? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

dm_exec_procedure_stats does it give query times?

Author

Topic

chris_lunt
Starting Member

25 Posts

Posted - 2014-02-13 : 04:30:31

Hello Folks

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.

Regards

Chris

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_APP_GET_ICM_RESULTS_FILTERED%' OR
qt.text LIKE '%USP_APP_GET_ICM_RESULTS_SUMMARY%' OR
qt.text LIKE '%USP_APP_GET_ICM_TREND_VIEW%'
ORDER BY
1

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-02-13 : 05:56:12

what are you looking for?
Is it Query execution ?
check this out
http://www.sqlteam.com/forums/topic.asp?topic_id=112286

Javeed Ahmed

chris_lunt
Starting Member

25 Posts

Posted - 2014-02-13 : 06:27:36

Cheers - do you think the above query I posted gives execution time though?

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-02-13 : 06:36:43

using sys.dm_exec_requests instead of sys.dm_exec_procedure_stats will give what you need.

Javeed Ahmed

chris_lunt
Starting Member

25 Posts

Posted - 2014-02-13 : 06:50:34

Cheers again - how will that dmv give me what I need?

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-02-13 : 07:12:16

Try this
select
a.session_id
,a.start_time
,a.status
,a.command
,db_name(a.database_id) as database_name
,a.blocking_session_id
,a.wait_type
,a.wait_time
,a.cpu_time
,a.total_elapsed_time
,b.text
from sys.dm_exec_requests a
cross apply sys.dm_exec_sql_text(a.sql_handle) b

Javeed Ahmed

chris_lunt
Starting Member

25 Posts

Posted - 2014-02-13 : 08:20:44

I'm still not sure how that would give me the query times. It seems to give the total time elapsed for something that's already running.

Are you able to reveiw the query I posted above and see if you think it gives average execution times? If ity does I'm planning on setting this up to execute on a regular basis and subtracting the previous results from the current results to get performance figures for every 5 minutes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources