Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

3 Answers

up vote 5 down vote accepted

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.

share|improve this answer
add comment

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/

share|improve this answer
    
I attempted to setup the alert as you suggested, however the job will only run if I have steps defined. Is there a way to setup the alert without defining job steps? –  Hasanain Aug 29 '11 at 15:35
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.