I'd like to know if there is a way to send a notification on deadlock? If so what queries would be required. I understand that SQL Server takes care of deadlocks, I simply would like information on the queries involved.
I found the following to determine long-running queries:
SELECT creation_time ,last_execution_time ,total_physical_reads ,total_logical_reads ,total_logical_writes , execution_count , total_worker_time , total_elapsed_time , total_elapsed_time / execution_count avg_elapsed_time ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st where total_elapsed_time >= 300000000 --5 min ORDER BY total_elapsed_time / execution_count DESC;
I'd like to know if the above is the right way to go, or is there a better way to determine if any query takes longer than a specific interval say 5 min as shown?