Every DBA has some tasks to prepare database health check
report on different database related key points i.e. database backup size
trend, database size trend, index utilization and fragmentation, dead lock
information, statistics report, event and error log, cache hit ratio and so on.
It is easy to get backup report, database sizes, indexes report, statistics report
event and error log but when it comes to deadlock information this is most
import question in every DBA’s mind. How can I get information about deadlocks
that happened last night. I will explain other tasks detail step by step in
different post but In this post I will try to explain about deadlock
Deadlocks happen when two or more processes trying to access
same source and both are holding locks on them. Both processes trying to
do some action on same instance and both are waiting for each other to complete
its action. It is hard to reproduce it in real time but I did it by force for
testing purpose.
Think of going shopping for furniture and two people (Person
A and Person B) go in at same time. Unknown to them they both want the
same goods (Good A and Good B). One starts searching from right another
person starts searching from left side of the store. Person A finds Good
A (Exclusive Lock) and starts looking for Good B (Shared Lock). But while
Person A was searching for Good A, Person B found Good B (Exclusive Lock) and
starts Looking for Good A (Shared Lock). When they find the other good
they realize it is already reserved and cannot have it, neither Person A nor
Person B is willing to let the other person have both the Goods. Therefore we
are stuck in a deadlock.
Let’s start this example by creating a new table as I
created a table (abc) with three columns with some default data.
Sample code Starts transaction with Begin Trans but there is no commit or rollback transaction. It
means this transaction is still doing some work and is busy and it has
(Exclusive) lock on table (abc).
In this query window we are trying to delete data from same
table that has a lock on it. This process goes on waiting state because of lock
on this table and it will go into waiting state using (Share Lock).
While both
transactions are open and waiting to complete action by each other process. I
execute another insert for table (abc) that will cause deadlock and with the
help of Deadlock Monitor Thread (default method to resolve deadlock issue) one
process will terminate to complete other.
SQL Server Profiler is used to get Server Side TraceEvent
Class: LocksEvent Name: Deadlock generates an XML Graph. Very easy
to read and figure out what is going on. Windows performance monitor provides
all the deadlocks that have happened on your server since the last
restart. To get dead lock information using following query
SELECT cntr_value
AS NumOfDeadLocks,*
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Locks'
AND counter_name = 'Number of Deadlocks/sec'
AND instance_name = '_Total'
Trace Flags1204 and 1222
Trace flag 1204 has existed since at least SQL Server 2000. And Trace Flag 1222 was introduced in SQL Server 2005.Both output Deadlock Information to the SQL Server ERRORLOG. You can enable trace flag setting using following query.
DBCC TRACEON (1222,-1)
You can review
trace status using following query
dbcc tracestatus (1222, 1204)
Once deadlock occurred it can be reviewed in
error log. Using following query you can review deadlock in detail.
EXEC sp_readerrorlog
System Health
System Health is like Default trace that exists in the
background. System Health has been around since 2008, however was not
visible in the GUI. Following query used to review deadlock information.
SELECT
xed.value('@timestamp', 'datetime2(3)') as CreationDate,
xed.query('.') AS XEvent
FROM
(
SELECT CAST([target_data] AS XML) AS TargetData
FROM sys.dm_xe_session_targets AS
st
INNER JOIN sys.dm_xe_sessions AS s
ON s.address = st.event_session_address
WHERE s.name = N'system_health'
AND
st.target_name = N'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (xed)
ORDER BY CreationDate DESC
We can click on any of the XML links to get the complete XML
information for deadlock and then save it as Filename.XDL file to get it in
Deadlock Graph in SSMS.
System Health is great, however it doesn’t save
deadlocks indefinitely. So next let’s look at implementing Deadlock
Monitoring using Extended Events, much like SQL Server Server Side Trace.
Extended Events
GUI Interface
for Extended events is in SQL Server 2012 RTM. However, after you create
the Extended Events Trace (Session) you can take the SQL code and run it
against 2008 or 2008 R2 to create a similar trace.
Using extended
event you can create a new Session. After setting up a Wizard you will get
deadlock information in mentioned files.
Following is the
query to create extended event session in 2008 and 2008 R2
CREATE EVENT SESSION [Deadlock_Monitor]
ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file(SET filename=N'C:\Deadlock\Deadlock_Monitor.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
Reviewing XML
detail for deadlock, you need to see XML document as in picture.
From the
attached picture you can see which two statements are involved in creating
deadlock. In the <Owner> tab process lock and type is mentioned. “X”
Exclusive for and “S” Shared in waiting state.
Deadlock Monitor Thread
In SQL Server to prevent this stalemate (i.e. a deadlock) from filling up the system, we have a Deadlock Monitor thread that is running in the background to “help” resolve deadlocks.
If we look
at
sys.dm_os_waiting_tasks we find a system task that is
always waiting: REQUEST_FOR_DEADLOCK_SEARCH. This thread wakes up every
five seconds to see if we have any deadlocks. If it finds any deadlocks,
it
terminates one of the sessions to keep all resources
available for other session. But it is a little tricky how SQL Server decide by
itself.
So one wonders
what happens when they are the same? It’s simple, SQL Server will kill
whoever came in second.It wakes up
every 5 seconds to check for deadlocks. If it finds any, it follows above
process to decide how to resolve it. However, first time it wakes up, it
wakes up the second time right away, to make sure it is not a nested
deadlock. If there is one it kills that and goes back to sleep.
Next time it wakes up it wakes up 4.90 seconds (estimate on wake, I think it’s
actually 10ms). It will keep going down to as small as 100ms; that is, it
will wake up 10 times per second to deal with Deadlock Issues.
Well,
SQL Server does have to worry a bit to resolve this scenario and decided which
session is going to kill. However it has to make sure to kill the session
that is the easiest to rollback. Because if SQL Server kills a
transactions, any work it has done must
be rolled back to bring the database to consistent state. It decided this
looking at the LOG USED value. Whichever session generate in last have less
used value for Log and it will be killed to release resources for other
process.