{"id":1893,"date":"2023-11-01T07:00:00","date_gmt":"2023-11-01T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1893"},"modified":"2024-10-14T12:45:40","modified_gmt":"2024-10-14T09:45:40","slug":"pos-syllegoyme-deadlocked-queries-meso-extended-event-kai-pos-dia","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-syllegoyme-deadlocked-queries-meso-extended-event-kai-pos-dia\/","title":{"rendered":"How we collect deadlocked queries via Extended Event and how we read its data"},"content":{"rendered":"

In older article<\/a> we had seen how we create Extended Event <\/strong>to see long-running queries. In this article we will build Extended Event that records the deadlocks<\/strong>.<\/p>\n\n\n\n

All we need to do is create it with a T-SQL command. After it is created, it will record in the path that we have declared to it in an XML file all the queries that lasted more than 1 second. <\/p>\n\n\n\n

To create the Extended Event<\/h4>\n\n\n\n

We must not forget to change the path to the one where we want the data to be stored.<\/p>\n\n\n\n

Then we execute it in a simple query window.<\/p>\n\n\n\n

CREATE EVENT SESSION [deadLock] ON SERVER \nADD EVENT sqlserver.xml_deadlock_report(\nACTION(package0.callstack,package0.collect_cpu_cycle_time,package0.collect_current_thread_id,package0.collect_system_time,package0.event_sequence,package0.last_error,package0.process_id,sqlos.cpu_id,sqlos.scheduler_id,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.query_plan_hash,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.session_server_principal_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.tsql_frame,sqlserver.username)) \nADD TARGET package0.event_file(SET filename=N'D:\\xevents\\deadlock.xel',max_file_size=(5))\nWITH (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)\nGO\n\nalter event session deadLock on server state=start;\ngo<\/pre>\n\n\n\n
How do we read the results?<\/h4>\n\n\n\n
In the path we defined during its creation XEvent <\/strong>a .xel and a .xem file were created. Through the dynamic view sys.fn_xe_file_target_read_file<\/strong> with parameters these files we result in all the XML <\/strong>file with the information.<\/p>\n\n\n\n
But since it is impractical to read an XML by eye, the file must somehow be filtered into a more readable form.<\/p>\n\n\n\n
So I sat down and made a query that does this job<\/p>\n\n\n\n
For starters, through the name we created, Xevent finds the path that is dynamically located and defines it as a parameter in the query.<\/p>\n\n\n\n
Then we define the XML as one Common Table Expression<\/strong>. By doing SELECT we can get the value from each property we need.<\/p>\n\n\n\n
In the where statement we can filter specific bases, the duration and the period of time it was executed.<\/p>\n\n\n\n
This part needs attention, as the default xml timestamp is in UTC timezone, so to put the actual time that the server has, we add the time difference that the server has with the UTC timezone (DATEADD(HOUR,DATEDIFF(hour, SYSUTCDATETIME(),SYSDATETIME() )<\/em>).<\/p>\n\n\n\n
declare\n\n@xel as varchar(max),\n@xem as varchar(max) \n\nset @xel=(select \n--n.name as XeventName,\nSUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xel' as xel\n--,SUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xem' as xem\nfrom sys.server_event_session_fields f\ninner join sys.server_event_sessions n on f.event_session_id = n.event_session_id\nWHERE f.NAME = 'filename' \nand n.name like '%deadLock%') --Set xevent Name\n\n\nset @xem=(select \n--n.name as XeventName,\n--SUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xel' as xel\nSUBSTRING(cast(f.value as varchar(max)),1,len(cast(f.value as varchar(max)))-4)+'*.xem' as xem\nfrom sys.server_event_session_fields f\ninner join sys.server_event_sessions n on f.event_session_id = n.event_session_id\nWHERE f.NAME = 'filename' \nand n.name like '%deadLock%')  --Set xevent Name\n\n;WITH XEvents AS\n(\nselect object_name, CAST(event_data AS XML) AS A FROM sys.fn_xe_file_target_read_file(\n@xel\n,@xem\n, NULL, NULL)\n)\nSELECT \n--object_name AS EventName,\nA.value ('(\/event\/action[@name=''database_name'']\/value)[1]', 'VARCHAR(MAX)') AS DB_Name,\nDATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),A.value ('(\/event\/@timestamp)[1]', 'DATETIME')) AS [Time],\n--A.value ('(\/event\/data\/value\/deadlock\/resource-list\/objectlock\/@dbid)[1]',  'VARCHAR(MAX)') AS dbid,\nd.name as DB_Name,\n\nA.value ('(\/event\/data\/value\/deadlock\/resource-list\/objectlock\/@objectname)[1]',  'VARCHAR(MAX)') AS locked_objectname,\nA.value ('(\/event\/data[@name=''xml_report'']\/value)[1]',  'VARCHAR(MAX)') AS All_Queries,\nA.value ('(\/event\/data\/value\/deadlock\/victim-list\/victimProcess\/@id)[1]',  'VARCHAR(MAX)') AS VictimProcess1,\nA.value ('(\/event\/data\/value\/deadlock\/victim-list\/victimProcess\/@id)[2]',  'VARCHAR(MAX)') AS VictimProcess2,\nA.value ('(\/event\/data\/value\/deadlock\/victim-list\/victimProcess\/@id)[3]',  'VARCHAR(MAX)') AS VictimProcess3,\nA.value ('(\/event\/data\/value\/deadlock\/victim-list\/victimProcess\/@id)[4]',  'VARCHAR(MAX)') AS VictimProcess4,\nA.value ('(\/event\/data\/value\/deadlock\/victim-list\/victimProcess\/@id)[5]',  'VARCHAR(MAX)') AS VictimProcess5,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@id)[1]',  'VARCHAR(MAX)') AS ListProcess1,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/inputbuf)[1]',  'VARCHAR(MAX)') AS ListQuery1,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@hostname)[1]',  'VARCHAR(MAX)') AS hostname1,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@currentdbname)[1]',  'VARCHAR(MAX)') AS Currentdbname1,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@loginname)[1]',  'VARCHAR(MAX)') AS Loginname1,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@lockMode)[1]',  'VARCHAR(MAX)') AS lockMode1,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@isolationlevel)[1]',  'VARCHAR(MAX)') AS isolationlevel1,\n\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/executionStack\/frame\/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle1,\nsf1.text as full_text1,\nqp1.query_plan as query_plan1,\n\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@id)[2]',  'VARCHAR(MAX)') AS ListProcess2,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/inputbuf)[2]',  'VARCHAR(MAX)') AS ListQuery2,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@hostname)[2]',  'VARCHAR(MAX)') AS hostname2,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@currentdbname)[2]',  'VARCHAR(MAX)') AS Currentdbname2,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@loginname)[2]',  'VARCHAR(MAX)') AS Loginname2,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@isolationlevel)[2]',  'VARCHAR(MAX)') AS isolationlevel12,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@lockMode)[2]',  'VARCHAR(MAX)') AS lockMode2,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process[2]\/executionStack\/frame\/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle2,\nsf2.text as full_text2,\nqp2.query_plan as query_plan2,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@id)[3]',  'VARCHAR(MAX)') AS ListProcess3,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/inputbuf)[3]',  'VARCHAR(MAX)') AS ListQuery3,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@lockMode)[3]',  'VARCHAR(MAX)') AS lockMode3,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@hostname)[3]',  'VARCHAR(MAX)') AS hostname3,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@currentdbname)[3]',  'VARCHAR(MAX)') AS Currentdbname3,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@loginname)[3]',  'VARCHAR(MAX)') AS Loginname3,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@isolationlevel)[3]',  'VARCHAR(MAX)') AS isolationlevel3,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process[3]\/executionStack\/frame\/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle3,\nsf3.text as full_text3,\nqp3.query_plan as query_plan3,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@id)[4]',  'VARCHAR(MAX)') AS ListProcess4,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/inputbuf)[4]',  'VARCHAR(MAX)') AS ListQuery4,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@lockMode)[4]',  'VARCHAR(MAX)') AS lockMode4,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@hostname)[4]',  'VARCHAR(MAX)') AS hostname4,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@currentdbname)[4]',  'VARCHAR(MAX)') AS Currentdbname4,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@loginname)[4]',  'VARCHAR(MAX)') AS Loginname4,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@isolationlevel)[4]',  'VARCHAR(MAX)') AS isolationlevel4,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process[4]\/executionStack\/frame\/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle4,\nsf4.text as full_text4,\nqp4.query_plan as query_plan4,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@id)[5]',  'VARCHAR(MAX)') AS ListProcess5,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/inputbuf)[5]',  'VARCHAR(MAX)') AS ListQuery5,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@lockMode)[5]',  'VARCHAR(MAX)') AS lockMode5,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@hostname)[5]',  'VARCHAR(MAX)') AS hostname5,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@loginname)[5]',  'VARCHAR(MAX)') AS Loginname5,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@isolationlevel)[5]',  'VARCHAR(MAX)') AS isolationlevel5,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process[5]\/executionStack\/frame\/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle5,\nsf5.text as full_text5,\nqp5.query_plan as query_plan5,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@id)[6]',  'VARCHAR(MAX)') AS ListProcess6,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/inputbuf)[6]',  'VARCHAR(MAX)') AS ListQuery6,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@lockMode)[6]',  'VARCHAR(MAX)') AS lockMode6,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@hostname)[6]',  'VARCHAR(MAX)') AS hostname6,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@loginname)[6]',  'VARCHAR(MAX)') AS Loginname6,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@currentdbname)[6]',  'VARCHAR(MAX)') AS Currentdbname6,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@isolationlevel)[6]',  'VARCHAR(MAX)') AS isolationlevel6,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process[6]\/executionStack\/frame\/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle6,\nsf6.text as full_text6,\nqp6.query_plan as query_plan6,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@id)[7]',  'VARCHAR(MAX)') AS ListProcess7,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/inputbuf)[7]',  'VARCHAR(MAX)') AS ListQuery7,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@lockMode)[7]',  'VARCHAR(MAX)') AS lockMode7,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@hostname)[7]',  'VARCHAR(MAX)') AS hostname7,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@loginname)[7]',  'VARCHAR(MAX)') AS Loginname7,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@currentdbname)[7]',  'VARCHAR(MAX)') AS Currentdbname7,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/@isolationlevel)[7]',  'VARCHAR(MAX)') AS isolationlevel7,\nA.value ('(\/event\/data\/value\/deadlock\/process-list\/process[7]\/executionStack\/frame\/@sqlhandle)[1]',  'VARCHAR(MAX)') AS sql_handle7,\nsf7.text as full_text7,\nqp7.query_plan as query_plan7,\nA   AS xml_report\n--,SUBSTRING(CAST(A as varchar(max)), CHARINDEX('<inputbuf>', CAST(A as varchar(max))), CHARINDEX('<\/inputbuf>',CAST(A as varchar(max))) - CHARINDEX('<inputbuf>', CAST(A as varchar(max))) + Len('<\/inputbuf>')) as VictimQuery,\n--SUBSTRING(CAST(A as varchar(max)), CHARINDEX('<inputbuf>',CAST(A as varchar(max)),CHARINDEX('<inputbuf>',CAST(A as varchar(max)))+10), CHARINDEX('<\/inputbuf>',CAST(A as varchar(max)),CHARINDEX('<\/inputbuf>',CAST(A as varchar(max)))+10) - CHARINDEX('<inputbuf>',CAST(A as varchar(max)),CHARINDEX('<inputbuf>',CAST(A as varchar(max)))+1) + Len('<\/inputbuf>')) as TheOtherQuery\nFrom XEvents x\nleft join sys.databases d on x.A.value ('(\/event\/data\/value\/deadlock\/resource-list\/objectlock\/@dbid)[1]',  'VARCHAR(MAX)') =d.database_id\n--left join  sys.dm_exec_query_stats qs1 on  qs1.sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1) \nouter apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf1\nouter apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp1\n\nouter apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process[2]\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf2\nouter apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process[2]\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp2\n\nouter apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process[3]\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf3\nouter apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process[3]\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp3\n\nouter apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process[4]\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf4\nouter apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process[4]\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp4\n\nouter apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process[5]\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf5\nouter apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process[5]\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp5\n\nouter apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process[6]\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf6\nouter apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process[6]\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp6\n\nouter apply sys.dm_exec_sql_text (CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process[7]\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)) as sf7\nouter apply sys.dm_exec_query_plan((select top 1 plan_handle from sys.dm_exec_query_stats where sql_handle = CONVERT(VARBINARY(MAX), x.A.value ('(\/event\/data\/value\/deadlock\/process-list\/process[7]\/executionStack\/frame\/@sqlhandle)[1]',  'varchar(max)'), 1)order by last_execution_time desc))as qp7\n\n\n\nwhere  1=1\nand DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),A.value ('(\/event\/@timestamp)[1]', 'DATETIME'))  between '2018-01-26 10:26:00.000' and '2018-12-26 10:28:00.000'\n--and d.name ='DB_1'<\/pre>\n\n\n\n
The result<\/h3>\n\n\n\n
The result of the query brought us for this period of time to the base where we asked him for the queries that were deadlocked, the list of queries along with the process id, the locking mode, the isolation level that each was running and finally what were the Victim <\/strong>that is, which were killed so that the rest could continue.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Sources:<\/h2>\n\n\n\n