How to monitor deadlocks in SQL Server (Deadlock)

SQL Server How to monitor the deadlock (Deadlock)

What is a deadlock?

The so-called deadlock : refers to two or more than two processes in the course of execution, because of competing resources or due to the communication between each other caused by a blocking phenomenon, if there is no external force, they will not be able to proceed. At this point the system is in a deadlock state or the system generates a deadlock, and these processes, which are always waiting on each other, are called deadlock processes.

Because resource consumption is mutually exclusive, when a process requests resources, so that the process without external assistance, can never allocate the necessary resources and can not continue to run, which creates a special phenomenon: deadlock.

In SQL Server in order to prevent a large number of deadlocks flooding in the system, we have a deadlock monitoring of the backend thread to help resolve the deadlock.

Deadlock Monitoring Thread

If we look at sys.dm_os_waiting_tasks, we can see that a system task has been waiting: Request_for_deadlock_search. The thread is woken every five seconds to see if there is a deadlock. If a deadlock is found, it ends a session. It kills one of the two sessions, letting the other session have all the resources needed.

SQL Server determines that the session that is most likely to be rolled back is killed. Because if SQL Server kills a transaction, any work it does must be rolled back to the synchronization state of the database. It is determined by the value of log used.

650) this.width=650; "title=" clip_image001 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image001 "src=" http://s3.51cto.com/wyfs02/M00/58/45/wKiom1StBaHADYLOAADKYvo0Qkk960.jpg "border=" 0 "height="/>

We can see that the above example kills session 75 instead of 192 because session 75 uses 648-word festive logs and session 192 uses 944 bytes.

The rear thread wakes up every five minutes to check for deadlocks. If found, it follows the process of the previous example to decide how to resolve it. However, when it wakes for the first time, it wakes up a second time, ensuring that it is not a nested deadlock. If there is, it will be killed and then returned to sleep. The next wakeup is after 4.9 seconds (the estimated wakeup time is 10 milliseconds). Each decrement of 100 milliseconds will wake up 10 times per second to handle deadlocks.

How do I monitor a deadlock?

Method One:

Windows Performance Monitor (Performance Monitor)

Object:SQLServer:Locks

Counter:number of Deadlocks/sec

Instance: _total

The following query provides all the deadlocks that have occurred on this server since the last restart:

SELECT Cntr_value as Numofdeadlocksfrom sys.dm_os_performance_counterswhere object_name = ' sqlserver:locks ' and counter _name = ' number of deadlocks/sec ' and instance_name = ' _total '

Method Two:

Trace flag (trace flags) 1204 and 1222

Trace Flag 1204 exists at least starting from SQL Server 2000. Trace Flag 1222 is included in SQL Server 2005. The deadlock information for both is logged to the SQL Server error log (errorlog).

Method Three:

SQL Trace on SQL Server Profiler and server side

Trace Event Class:locks Event Name:deadlock Graph

Give an XML diagram as shown in the example above. It's very easy to read and find out what's currently going on.

Method Four:

Extended event (Extended events)

New ways to monitor since SQL Server 2008 started. Extended events will eventually replace SQL Server Profiler (note: SQL Server Profiler is in the list of discarded attributes). As with SQL Server Profiler, it provides the same XML diagram and is more lightweight in performance impact.

Method Five:

System Health

A new default trace, but it does not have a limited number of trace information and cannot be modified like SQL Server default trace. We can modify the definition of system health, which is built into the extended event. Unlike the default trace, System health can track the deadlock information that has just occurred. We can obtain this information from System Health for analysis without deploying our own extended event monitoring.

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1600374

How to monitor deadlocks in SQL Server (Deadlock)