Notification on long-running query or deadlock in SQL Server 2008 R2?

Question

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?

Thanks

Answer 1

With SQL 2008 there is a new feature that you can use for deadlocks and long running queires: extended events. Extended events are low level objects and consume much lesser resources than other methods like profiling/tracing, alerts, etc...

For using extended events with deadlocks check out this post by Jonathan Kehayias, a SQL server MVP.

For using extended events to find long running queries, check out this detailed post by Pinal Dave, another SQL server MVP.

Answer 2

You can configure alerts for both of these with SQL Agent. Create a new alert and select type "SQL Server performance condition alert"

For long running queries, choose Object "MSSQL$InstanceName:Transactions" and Counter: Longest Transaction Running Time. Configure the values, and the alert notification options, and you're good to go.

For deadlocks, the Object is "MSSQL$InstanceName:Locks" and the Counter is "Number of Deadlocks/sec"

If you want more fine grained control of the deadlock notification, check this out: http://www.sqlservercentral.com/articles/Administration/3243/

Answer 3

I would favour @StanleyJohns suggestion if you have SQL2008. Extended events are worth familiarising yourself with as a diagnostic tool and Jonathon's An XEvent a Day series is a great place to start.

An alternative for deadlocks is to enable trace flags 1204 and 1222, which dump deadlock information to the SQL error log. Enable both, so you get the information in two differing formats, which can make it easier to understand complex deadlock chains.