Check for deadlocks - best method in SQL server 2012

sherlock1
sherlock1 used Ask the Experts™
on
Hi Experts,

We are running SQL server 2012 on a Windows server running Windows server 2012 standard 64bit in an SQL cluster setup with two server nodes (Both nodes running same O/S  and SQL server version).

We would like to check for deadlocks in the SQL database. Is there a script or method in which you would recommended to do this? and do we need to turn on any flags or enable anything in the SQL server in order to record deadlocks? or in SQL server 2012 is recording deadlocks enabled as standard?

Since there SQL database is reachable on both nodes I presume the deadlocks can be checked from either server node? or would both nodes need to be checked individually?

Users have encountered performance related issues recently in the application that connected to the SQL server and we would like to check for any deadlocks / or at least rule this out as being a cause of the issue if there are not any deadlocks

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
there are several ways to track deadlocks-- > your code error handler;

sql profiler; -
Analyze Deadlocks with SQL Server Profile
 https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/analyze-deadlocks-with-sql-server-profiler
--
sql traces  
Dbcc traceon (1204,-1) -.> https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx
DBCC Traceon (1222,-1) -- >Output is captured in the SQL Server error log.

Extended Events ( https://msdn.microsoft.com/en-us/library/bb630354(v=sql.105).aspx )


you should start from MSFT source:

#1 what is Deadlocking?
https://technet.microsoft.com/en-us/library/ms177433(v=sql.105).aspx
#2 Detecting and Ending Deadlocks
https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx 
#3 Handling Deadlocks
https://technet.microsoft.com/en-us/library/ms177453(v=sql.105).aspx
#4 Minimizing Deadlocks
https://technet.microsoft.com/en-us/library/ms191242(v=sql.105).aspx
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
Just to be sure, do you really want to check for deadlocks of blocking processes?
I'm asking because people are usually confusing both.
With deadlocks you can have only information that it happened but you can't do nothing after receiving the warning as SQL Server engine will solve that for you.
With blocking processes is different as you usually have an action to perform after receiving the warning message as you need to solve it manually.

Author

Commented:
Thanks for your replies

Vitor - hmm checking for blocking processes would be good too - maybe that would be of more benefit. and may well be of more use
Thanks for questioning this. So to clarify do deadlocks resolve themselves and its not possible to manually resolve them?
wheres blocking processes must be resolved manually? any further clarification would be great

Eugene - Thanks for the information and those weblinks which are very useful

Are you saying in order to see deadlocks I have to run the below to turn on logging the events? or is this only needed if I want to run SQL traces? and its possible to see deadlocks with other methods without turning these traces on?
Dbcc traceon (1204,-1)
DBCC Traceon (1222,-1)

Thanks
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
Sherlock, check this MSDN article to find the differences and how to avoid/reduce the number of deadlocks and blocking processes.
Commented:
you can turn off trace 1222 anytime

however, for Azure (probably not you case ) you can try to use this query  ( more from https://blogs.msdn.microsoft.com/sqldatabasetalk/2013/05/01/tracking-down-deadlocks-in-sql-database/)
SELECT * FROM sys.event_log


WHERE database_name like 'yourdb'


AND event_type = 'deadlock
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Recommendation to close this question by accepting the above comments as solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial