sys_dm_exec_requests - Total Elapsed Time

Question

sys_dm_exec_requests - Total Elapsed Time

jvkondapalli

SSCommitted

Points: 1587
More actions
November 7, 2012 at 1:22 pm

#270563

Gurus,
In our OLTp systems, we have a job which will capture queries running more than 1 min and report to us. The job runs every 1 minute through sql agent.
We take the start_time from sys.dm_exec_requests dmv and do a datediff with current date time to deduce the time take to run the query.
But now, we have a cdc switched-on on one of our tables. When ever sp_cdc_scan proc kicks the start_time is the time when sql was started. Because of this we are not able to monitor sp_cdc_scan proc.(not that we are doubting MS credibility in writing this proc. and we certaining dont want to dig into this proc for performance tuning..) We just want to get alerted if this runs more than 1 minute.
We looked into total_elapsed_time too. But seems like this column clocks the time for parallelism as well. I am wondering if there is a way to use total_elapsed_time and deduce the actual time of execution since the last time the proc woke up to do anything.
Any other suggestions would also be helpful. Please reply at your earliest convenience
I hope I am clear in my question.
Thank you
Jagan K
Thanks
Jagan K

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

jvkondapalli (11/7/2012)
Gurus,
In our OLTp systems, we have a job which will capture queries running more than 1 min and report to us. The job runs every 1 minute through sql agent.
We take the start_time from sys.dm_exec_requests dmv and do a datediff with current date time to deduce the time take to run the query.
But now, we have a cdc switched-on on one of our tables. When ever sp_cdc_scan proc kicks the start_time is the time when sql was started. Because of this we are not able to monitor sp_cdc_scan proc.(not that we are doubting MS credibility in writing this proc. and we certaining dont want to dig into this proc for performance tuning..) We just want to get alerted if this runs more than 1 minute.
We looked into total_elapsed_time too. But seems like this column clocks the time for parallelism as well. I am wondering if there is a way to use total_elapsed_time and deduce the actual time of execution since the last time the proc woke up to do anything.
Any other suggestions would also be helpful. Please reply at your earliest convenience
I hope I am clear in my question.
Thank you
Jagan K

The entire method you are using is flawed and prone to missing events by polling. If a session has been running for 45 seconds at the point your job runs it won't be reported. If it runs for another 45 seconds and terminates before the next job run, you lost that significant event. The correct way for doing this type of data collection is a server side trace to a file with a filter on Duration > 1 minute. If you are 2012, you could use Extended Events, I wouldn't on 2008 because of limitations in the events and the data produced by them. If you want automated alerting, a .NET application using multiple threads could easily be written using SMO to provide that capability. It wouldn't take much code to make that a fully functional solution to what you want.

jvkondapalli SSCommitted Points: 1587 More actions · Answer 2

Thank you Johnathan. Extended Events was my biggest take away from Devconnections conference last week. Will implement that.

Thanks again

Jagan K

Thanks
Jagan K