dm_exec_procedure_stats is this correct? - 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

Transact-SQL (2008)

dm_exec_procedure_stats is this correct?

Author

Topic

chris_lunt
Starting Member

25 Posts

Posted - 2014-02-13 : 10:11:54

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.

If it does work I'm planning on creating a procedure that runs at regular intervals and compares the last results with the current results to get the execution times for the interval (say 5 minutes) then I can monitor performance over time.

If I get to that point I'd be happy to share it with you.

Problem is - I'm not 100% that I have the cirrect understanding of this DMV.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources