Challenge:
We want to know if the application creates deadlocks.
Solution:
Create an Extended Event in XML server and start this Event.
What will it do:
I will write a report when two sqlserver session are trying to update the same record.
What will it benefit for?:
We can identify application workflow mistakes and fix them in the application. Or we will be informed when there is really bad database performance.
How do you implement it:
Run this in management studio:
–before executing this code, adjust the PATH where the package0.event_file writes it XML in case a deadlock occurs
CREATE EVENT SESSION [Collect-Deadlock] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report(
ACTION(
package0.collect_system_time,
sqlos.task_time,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_id,
sqlserver.is_system,
sqlserver.username
)
)
ADD TARGET package0.event_file(
SET filename=N’S:MSSQLDATACollect-Deadlock.xel’
)
WITH (
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=20 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=ON
) ;
Where can you find it in management studio:
Clicking on the XML_report field will give more info about the queries that caused the deadlock.
<deadlock>
<victim-list>
<victimProcess id=”process199be923c28″ />
</victim-list>
<process-list>
<process id=”process199be923c28″ taskpriority=”0″ logused=”304″ waitresource=”KEY: 13:72057594043170816 (8194443284a0)” waittime=”3931″ ownerId=”242631086″ transactionname=”user_transaction” lasttranstarted=”2021-09-09T14:25:23.407″ XDES=”0x19b40fc4428″ lockMode=”U” schedulerid=”10″ kpid=”11196″ status=”suspended” spid=”58″ sbid=”0″ ecid=”0″ priority=”0″ trancount=”2″ lastbatchstarted=”2021-09-09T14:25:44.250″ lastbatchcompleted=”2021-09-09T14:25:44.250″ lastattention=”1900-01-01T00:00:00.250″ clientapp=”Microsoft SQL Server Management Studio – Query” hostname=”SQL-LOADTEST” hostpid=”12048″ loginname=”bla” isolationlevel=”read committed (2)” xactid=”242631086″ currentdb=”13″ currentdbname=”testdb” lockTimeout=”4294967295″ clientoption1=”671090784″ clientoption2=”390200″>
<executionStack>
<frame procname=”adhoc” line=”1″ stmtend=”174″ sqlhandle=”0x02000000d1fd68102148fde786421167f4833b3bbeef63e60000000000000000000000000000000000000000″>
unknown </frame>
</executionStack>
<inputbuf>
UPDATE table1
SET student_name = student_name + ‘Transaction2’
WHERE id IN (1,2,3,4,5) </inputbuf>
</process>
<process id=”process192b3b104e8″ taskpriority=”0″ logused=”928″ waitresource=”KEY: 13:72057594043236352 (8194443284a0)” waittime=”15838″ ownerId=”323″ transactionname=”user_transaction” lasttranstarted=”2021-09-09T14:25:02.860″ XDES=”0x1991b430428″ lockMode=”X” schedulerid=”10″ kpid=”7388″ status=”suspended” spid=”124″ sbid=”0″ ecid=”0″ priority=”0″ trancount=”2″ lastbatchstarted=”2021-09-09T14:25:32.350″ lastbatchcompleted=”2021-09-09T14:25:32.340″ lastattention=”2021-09-09T14:13:47.553″ clientapp=”Microsoft SQL Server Management Studio – Query” hostname=”S3-SQL-LOADTEST” hostpid=”8524″ loginname=” bla” isolationlevel=”read committed (2)” xactid=”242626607″ currentdb=”13″ currentdbname=”testdb” lockTimeout=”4294967295″ clientoption1=”671090784″ clientoption2=”390200″>
<executionStack>
<frame procname=”adhoc” line=”1″ stmtstart=”58″ stmtend=”194″ sqlhandle=”0x0200000005d82111fed181ee6a93eb75f78ab596ffe48b130000000000000000000000000000000000000000″>
unknown </frame>
<frame procname=”adhoc” line=”1″ stmtend=”152″ sqlhandle=”0x020000001ab3f03a6e49070e79093e95d07bd73a660d8aa30000000000000000000000000000000000000000″>
unknown </frame>
</executionStack>
<inputbuf>
UPDATE table2
SET student_name = student_name + ‘Transaction1’
WHERE id = 1 </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid=”72057594043170816″ dbid=”13″ objectname=”testdb.dbo.table1″ indexname=”PK__table1__3213E83F31F7B99D” id=”lock1933e329980″ mode=”X” associatedObjectId=”72057594043170816″>
<owner-list>
<owner id=”process192b3b104e8″ mode=”X” />
</owner-list>
<waiter-list>
<waiter id=”process199be923c28″ mode=”U” requestType=”wait” />
</waiter-list>
</keylock>
<keylock hobtid=”72057594043236352″ dbid=”13″ objectname=”testdb.dbo.table2″ indexname=”PK__table2__3213E83FE5349495″ id=”lock19a989db700″ mode=”X” associatedObjectId=”72057594043236352″>
<owner-list>
<owner id=”process199be923c28″ mode=”X” />
</owner-list>
<waiter-list>
<waiter id=”process192b3b104e8″ mode=”X” requestType=”wait” />
</waiter-list>
</keylock>
</resource-list>
</deadlock>