asked
Monitoring how to.
What is best way to monitor things that are taking longer thirty seconds to render? For instance long running stored procedures or views.
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
At the most basic level, I would check sys.dm_exec_requests where total_elapsed_time > 30000 (or datediff(second, start_time, getdate() > 30 if you want to go that route)- [
https://msdn.microsoft.com/en-us/library/ms177648.aspx][1] What do you want to happen if something lasts more than 30 seconds? You could log the results to a table or email the results as a form of alert which is something I sometimes do if the long running query is also blocking other queries. If you don't want to roll your own, several 3rd party tools can monitor and alert for long running queries if you don't mind purchasing something. [1]:
https://msdn.microsoft.com/en-us/library/ms177648.aspx
[I would us Extended Events][1]. You can capture both stored procedures through rpc_complete and any type of ad hoc query through sql_batch_complete. With the filtering available you can decide to only capture those events that run longer than 30 seconds. Once set up, it's completely automated. You just have to read the output. [1]:
http://blogs.msdn.com/b/extended_events/
17 People are following this question.
