{"id":2754,"date":"2015-03-31T12:14:25","date_gmt":"2015-03-31T11:14:25","guid":{"rendered":"http:\/\/andreas-wolter.com\/import-von-extended-events-ereignisdateien-und-parsen-von-deadlock-graphen\/"},"modified":"2017-10-18T11:25:55","modified_gmt":"2017-10-18T10:25:55","slug":"how-to-import-extended-events-session-event_file-target","status":"publish","type":"post","link":"https:\/\/andreas-wolter.com\/en\/how-to-import-extended-events-session-event_file-target\/","title":{"rendered":"How to import Extended Events session event_file target and parse deadlock-graph"},"content":{"rendered":"\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_heading-f35acd4601ac19370418a943f51dcf16\">\n#top .av-special-heading.av-av_heading-f35acd4601ac19370418a943f51dcf16{\npadding-bottom:10px;\n}\nbody .av-special-heading.av-av_heading-f35acd4601ac19370418a943f51dcf16 .av-special-heading-tag .heading-char{\nfont-size:25px;\n}\n.av-special-heading.av-av_heading-f35acd4601ac19370418a943f51dcf16 .av-subheading{\nfont-size:15px;\n}\n<\/style>\n<div  class='av-special-heading av-av_heading-f35acd4601ac19370418a943f51dcf16 av-special-heading-h3 blockquote modern-quote  avia-builder-el-0  el_before_av_textblock  avia-builder-el-first '><h3 class='av-special-heading-tag'  itemprop=\"headline\"  >How to import Extended Events session event_file target and parse deadlock-graph <\/h3><div class=\"special-heading-border\"><div class=\"special-heading-inner-border\"><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>This time it\u2019s about one of the things that <strong>Extended Events <\/strong>do better and more easily than all techniques before: Saving of <strong>deadlock-events<\/strong> that occurred and analyzing the respective <strong>deadlock-graph<\/strong>.<!--more--><\/p>\n<p><strong>Deadlocks<\/strong>, explained <a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/ms178104%28v=sql.105%29.aspx\" target=\"_blank\" rel=\"noopener\">here<\/a>, occur between at least 2 threads but often more than 2 are involved. &#8211; I am using the term \u201cthread\u201d on purpose, since process would not be sufficiently accurate. Mostly Deadlocks in fact do happen between different sessions\/processes, but it is also possible that a process running with parallel threads blocks itself. The error number for deadlocks is \u201c<strong><em>1205<\/em><\/strong>\u201d \u2013 probably known to all SQL professionals.<\/p>\n<p>For <strong>tracing deadlocks before SQL Server 2008<\/strong> there were merely the following ways:<\/p>\n<p style=\"padding-left: 30px;\">1. A <strong>SQL<\/strong> <strong>Trace<\/strong> with <strong>Profiler<\/strong> or without GUI with logging in a trace-file or live inside the GUI<\/p>\n<p style=\"padding-left: 30px;\">2.<strong> Trace Flags<\/strong> 1204, 1205 or 1222, which log into SQL Server\u2019s errorlog \u2013 \u201egreat\u201c especially on \u201ceventful\u201d servers.<\/p>\n<p>Some may remember those horrible <strong>entries in the error log<\/strong>:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-dc62a0b2ce31561f3d161a90a5142170\">\n.avia-image-container.av-av_image-dc62a0b2ce31561f3d161a90a5142170 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-dc62a0b2ce31561f3d161a90a5142170 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-dc62a0b2ce31561f3d161a90a5142170 av-styling- avia-align-center  avia-builder-el-2  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2731 avia-img-lazy-loading-not-2731 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_SQL_Deadlock_Graph_in_ErrorLog.png\" alt='' title='1503_SQL_Deadlock_Graph_in_ErrorLog'  height=\"594\" width=\"605\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_SQL_Deadlock_Graph_in_ErrorLog.png 605w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_SQL_Deadlock_Graph_in_ErrorLog-600x589.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_SQL_Deadlock_Graph_in_ErrorLog-80x80.png 80w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_SQL_Deadlock_Graph_in_ErrorLog-300x295.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_SQL_Deadlock_Graph_in_ErrorLog-36x36.png 36w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_SQL_Deadlock_Graph_in_ErrorLog-450x442.png 450w\" sizes=\"(max-width: 605px) 100vw, 605px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><ol start=\"3\">\n<li><strong>Event Notifications<\/strong> which provide the data via Service Broker Queue in XML-format. <a href=\"http:\/\/www.sswug.org\/databases\/default.aspx?TargetID=64385\" target=\"_blank\" rel=\"noopener\">Her<\/a><u>e<\/u> you find an elaborate example with storing in table and automatic email with information about the Event.<\/li>\n<li>A <strong>WMI-Alert<\/strong> which also provides the data via XML und tied to a job can also log respectively inform about it.<\/li>\n<\/ol>\n<p>All those approaches, as different as their respective advantages may, have one thing in common: They have to be set up. In case of doubt after the first deadlocks occurred.<\/p>\n<p>But since SQL Server 2008 all <strong>deadlocks are being logged by default<\/strong>.<\/p>\n<p>This is done by the automatically running <strong><em>system_health Extended Event session<\/em><\/strong>.<\/p>\n<p>This, alongside deadlocks logs further important server events and error-information. <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ff877955.aspx\" target=\"_blank\" rel=\"noopener\">Here you find details on it<\/a>.<\/p>\n<p>Since SQL Server 2012 the system_health session apart from the memory-target \u201cring_buffer\u201d also logs into an event-file, so that deadlocks and other error stay available even after a server restart for quite a while (up to a few days) \u2013 until the rollover of the 5 files has occurred.<\/p>\n<p><strong>For SQL Server 2008 instances I recommend adding the file-target<\/strong>.<\/p>\n<p>How can you read-out that data? This works quite easily via T-SQL once you know that this <strong>data is available via the DMV <\/strong><a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/bb677313.aspx\" target=\"_blank\" rel=\"noopener\"><strong>sys.<\/strong> <strong>dm_xe_session_targets<\/strong><\/a>. This is how you read-out the ring_buffer-Target.<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>SELECT<\/p>\n<p style=\"padding-left: 30px;\">\u00a0\u00a0 target_<strong>data<\/strong><\/p>\n<p>FROM<\/p>\n<p style=\"padding-left: 30px;\"><strong>\u00a0\u00a0 sys.dm_xe_session_targets<\/strong> AS dm_xe_session_targets<\/p>\n<p>INNER JOIN <strong>sys.dm_xe_sessions<\/strong> AS dm_xe_sessions<\/p>\n<p style=\"padding-left: 30px;\">\u00a0\u00a0 ON dm_xe_session_targets.event_session_address = dm_xe_sessions.address<\/p>\n<p>WHERE<\/p>\n<p style=\"padding-left: 30px;\">\u00a0\u00a0 <strong>dm_xe_sessions.name = &#8216;system_health&#8217;<\/strong><\/p>\n<p style=\"padding-left: 30px;\">\u00a0\u00a0AND <strong>target_name = &#8216;ring_buffer&#8217;<\/strong><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>\u201eTarget_data\u201c is XML-text stored as unicode-text-blob which can even be opened directly in SSMS after converting to XML. <strong>To filter out only the deadlocks<\/strong> you need to use the <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms189075.aspx\" target=\"_blank\" rel=\"noopener\"><strong>XQuery<\/strong><\/a>-extensions of T-SQL. And this is how that looks like in code plus results:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-3e64c63f443d4d428e5605bb01351208\">\n.avia-image-container.av-av_image-3e64c63f443d4d428e5605bb01351208 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-3e64c63f443d4d428e5605bb01351208 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-3e64c63f443d4d428e5605bb01351208 av-styling- avia-align-center  avia-builder-el-6  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2733 avia-img-lazy-loading-not-2733 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_reports.png\" alt='' title='1503_system_health_deadlock_reports'  height=\"488\" width=\"786\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_reports.png 786w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_reports-600x373.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_reports-300x186.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_reports-768x477.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_reports-705x438.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_reports-450x279.png 450w\" sizes=\"(max-width: 786px) 100vw, 786px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>When clicking at one of those <strong>Deadlock-Report<\/strong> reports in XML-format in SSMS it will be opened in a new window as <strong>XML-document<\/strong>:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-e73698aafc7f296a9cffaa750c320a21\">\n.avia-image-container.av-av_image-e73698aafc7f296a9cffaa750c320a21 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-e73698aafc7f296a9cffaa750c320a21 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-e73698aafc7f296a9cffaa750c320a21 av-styling- avia-align-center  avia-builder-el-8  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2735 avia-img-lazy-loading-not-2735 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_DeadlockGraph_xml.png\" alt='' title='1503_system_health_DeadlockGraph_xml'  height=\"507\" width=\"989\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_DeadlockGraph_xml.png 989w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_DeadlockGraph_xml-600x308.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_DeadlockGraph_xml-300x154.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_DeadlockGraph_xml-768x394.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_DeadlockGraph_xml-705x361.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_DeadlockGraph_xml-450x231.png 450w\" sizes=\"(max-width: 989px) 100vw, 989px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>&#8211; in the picture I collapsed some nodes for brevity.<\/p>\n<p>Most important here are: the <strong>victim-list<\/strong>, which for a multi-victim-deadlock contains one process-id per victim, which again one can investigate more deeply in the <strong><em>process-list<\/em><\/strong>.<\/p>\n<p>In the <strong><em>executionStack<\/em><\/strong> one can find the executes SQL-command and if applicable even the procedure name.<\/p>\n<p>Below in the <strong><em>resource-list<\/em><\/strong> follows a list of all involved (blocked) resources.<\/p>\n<p>All nodes are peppered with details such as row number, objectname, lock-mode, waiting time, spid and of course timestamps.<\/p>\n<p>Who wants to start from here can of course take apart the XML further and try to break individual nodes relationally into columns. For that one has to first <strong>extract the deadlock-graph<\/strong>.<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>CAST(XEventData.XEvent.query(&#8216;(data\/value\/deadlock)[1]&#8217;) AS xml) AS DeadlockGraph<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-full wp-image-2736\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_graph.png\" alt=\"\" width=\"605\" height=\"375\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_graph.png 605w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_graph-600x372.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_graph-300x186.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_graph-450x279.png 450w\" sizes=\"auto, (max-width: 605px) 100vw, 605px\" \/><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>From there one can easily\u00a0<strong>parse the individual nodes<\/strong>. The transaction isolation level for example can be reached with the <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms178030.aspx\" target=\"_blank\" rel=\"noopener\">value-method<\/a> once you know on which level\/in which node in the graph it resides.<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><em>XMLCol<\/em>.value(&#8216;(\/deadlock\/process-list\/process)[1]\/@isolationlevel&#8217;, &#8216;varchar(100)&#8217;)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-full wp-image-2738\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_graphs_parsed_sample.png\" alt=\"\" width=\"864\" height=\"431\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_graphs_parsed_sample.png 864w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_graphs_parsed_sample-600x299.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_graphs_parsed_sample-300x150.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_graphs_parsed_sample-768x383.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_graphs_parsed_sample-705x352.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_system_health_deadlock_graphs_parsed_sample-450x224.png 450w\" sizes=\"auto, (max-width: 864px) 100vw, 864px\" \/><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>In this manner one can proceed with almost all attributes and elements. For locking-information one will get to the <strong>limits of the static relational representation<\/strong> since depending on the locked resource there will be completely different attributes.<\/p>\n<p>The <strong>second problem <\/strong>arises once you are dealing with a <strong>multi-victim-deadlock<\/strong>.<\/p>\n<p>Potentially there can be a vast number of locked and killed victim-processes, so that one can hardly prepare an ever sufficient schema.<\/p>\n<p>Here an <strong>example of a multi-victim-deadlock<\/strong>, displayed with the XEvents GUI (Profiler could not handle that as many other things)<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-735a5a50009f68c944e4d856f623d952\">\n.avia-image-container.av-av_image-735a5a50009f68c944e4d856f623d952 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-735a5a50009f68c944e4d856f623d952 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-735a5a50009f68c944e4d856f623d952 av-styling- avia-align-center  avia-builder-el-14  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2741 avia-img-lazy-loading-not-2741 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_5_Process_Multivictim_Deadlock.jpg\" alt='' title='1502_5_Process_Multivictim_Deadlock'  height=\"247\" width=\"1251\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_5_Process_Multivictim_Deadlock.jpg 1251w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_5_Process_Multivictim_Deadlock-600x118.jpg 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_5_Process_Multivictim_Deadlock-300x59.jpg 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_5_Process_Multivictim_Deadlock-768x152.jpg 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_5_Process_Multivictim_Deadlock-1030x203.jpg 1030w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_5_Process_Multivictim_Deadlock-705x139.jpg 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_5_Process_Multivictim_Deadlock-450x89.jpg 450w\" sizes=\"(max-width: 1251px) 100vw, 1251px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Last month I published my <strong>SQL Deadlock-Collector &#038; Parser<\/strong> <strong>freely downloadable<\/strong> at codeplex. &#8211; here the direct link: : <a href=\"https:\/\/sqldeadlockcollector.codeplex.com\/releases\" target=\"_blank\" rel=\"noopener\"><strong>SQL Deadlock Collector &#038; Parser<\/strong><\/a><\/p>\n<p>&#8211; here the article with detailed explanation:<\/p>\n<p><a href=\"http:\/\/www.insidesql.org\/blogs\/andreaswolter\/2015\/02\/free-deadlock-collector-parser-based-on-extended-events-system_health-session\" target=\"_blank\" rel=\"noopener\"><strong>free Deadlock-Collector &#038; -Parser based on Extended Events<\/strong><\/a><\/p>\n<p>There I decided to always contrast the first two processes, which helps for most deadlocks a lot already. This is how it looks like:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-full wp-image-2742\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_DeadlockCollector_AffectedProcesses_ProcedureName_InputBuffer.png\" alt=\"\" width=\"1312\" height=\"129\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_DeadlockCollector_AffectedProcesses_ProcedureName_InputBuffer.png 1312w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_DeadlockCollector_AffectedProcesses_ProcedureName_InputBuffer-600x59.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_DeadlockCollector_AffectedProcesses_ProcedureName_InputBuffer-300x29.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_DeadlockCollector_AffectedProcesses_ProcedureName_InputBuffer-768x76.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_DeadlockCollector_AffectedProcesses_ProcedureName_InputBuffer-1030x101.png 1030w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_DeadlockCollector_AffectedProcesses_ProcedureName_InputBuffer-705x69.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1502_DeadlockCollector_AffectedProcesses_ProcedureName_InputBuffer-450x44.png 450w\" sizes=\"auto, (max-width: 1312px) 100vw, 1312px\" \/><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>How is it possible to <strong>read-in the eventfiles<\/strong> which are written to as well by the system_health-session?<\/p>\n<p>For that the system-function <a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/cc280743.aspx\" target=\"_blank\" rel=\"noopener\"><strong><em>sys.fn_xe_file_target_read_file<\/em><\/strong><\/a> is a provided. It needs the path to the <strong>xel-file<\/strong> and the name of the first file which is to be read-in. The files are located in the log-directory of the SQL Server instance by the way. If the filename is not specified all files in the directory will be read-in. What makes the function really handy is that one can already arm the path with a wildcard so that practically one does not even need the filename any more.<\/p>\n<p>This is how that can look like:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-6876eb26807d98866babda31deb148a7\">\n.avia-image-container.av-av_image-6876eb26807d98866babda31deb148a7 img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-6876eb26807d98866babda31deb148a7 .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-6876eb26807d98866babda31deb148a7 av-styling- avia-align-center  avia-builder-el-17  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2745 avia-img-lazy-loading-not-2745 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_fn_xe_file_target_read_file_system_health_deadlocks.png\" alt='' title='1503_fn_xe_file_target_read_file_system_health_deadlocks'  height=\"294\" width=\"964\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_fn_xe_file_target_read_file_system_health_deadlocks.png 964w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_fn_xe_file_target_read_file_system_health_deadlocks-600x183.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_fn_xe_file_target_read_file_system_health_deadlocks-300x91.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_fn_xe_file_target_read_file_system_health_deadlocks-768x234.png 768w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_fn_xe_file_target_read_file_system_health_deadlocks-705x215.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_fn_xe_file_target_read_file_system_health_deadlocks-450x137.png 450w\" sizes=\"(max-width: 964px) 100vw, 964px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Those who are already using the <strong><em>Deadlock-Collector<\/em><\/strong> to parse and save ones deadlocks can also use it to import deadlocks from any systems into the <em>SQL_Analysis_Data<\/em>-database.<\/p>\n<p>For that simply use the procedure <strong><em>Locking.ins_DeadLock<\/em><\/strong> in the database <strong><em>SQL_Analysis_Code<\/em><\/strong> with the parameter <strong><em>@XESource<\/em><\/strong>.<\/p>\n<p>It is set to use the ring_buffer by default. But one can simply pass in the path + filename of the xel-file to import:<\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>USE SQL_Analysis_Code;<\/p>\n<p>DECLARE @DeadlocksCollected int;<\/p>\n<p><strong>EXECUTE Locking.ins_DeadLock<\/strong><\/p>\n<p><strong>\u00a0\u00a0 @XESource = &#8216;D:LogFilesSQLServerSQL2012Devsystem_health_*.xel&#8217;<\/strong><\/p>\n<p>,\u00a0\u00a0 @DeadlocksCollected = @DeadlocksCollected OUTPUT;<\/p>\n<p>SELECT @DeadlocksCollected AS [Number of new Deadlocks collected:];<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignleft size-full wp-image-2746\" src=\"http:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_Deadlock_Collector_ins_DeadLock_XESource_XEFile.png\" alt=\"\" width=\"669\" height=\"206\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_Deadlock_Collector_ins_DeadLock_XESource_XEFile.png 669w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_Deadlock_Collector_ins_DeadLock_XESource_XEFile-600x185.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_Deadlock_Collector_ins_DeadLock_XESource_XEFile-300x92.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_Deadlock_Collector_ins_DeadLock_XESource_XEFile-450x139.png 450w\" sizes=\"auto, (max-width: 669px) 100vw, 669px\" \/><\/p>\n<\/div><\/section>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Afterwards one can comfortably analyze the deadlocks via views or ad-hoc T-SQL in a half-way relational structure as <a href=\"http:\/\/www.insidesql.org\/blogs\/andreaswolter\/2015\/02\/free-deadlock-collector-parser-based-on-extended-events-system_health-session\" target=\"_blank\" rel=\"noopener\">described here<\/a>.<\/p>\n<p><strong>Note:<\/strong><\/p>\n<p>It is possible that deadlocks occur which do not appear in the system_health-session targets. Neither in the ring_buffer nor in the filetarget. This happens when the deadlock-graph is too big for the buffer-memory.<\/p>\n<p><strong>In order to make sure that larger deadlock graphs are also captured, I recommend increasing the maximal work memory of the system_health session.<\/strong><\/p>\n<p>And those who are still using Profiler &#038; SQL trace now\u00a0(<a href=\"http:\/\/www.insidesql.org\/blogs\/andreaswolter\/2013\/11\/extended-events-vs-sql-trace-comparison-top-features\" target=\"_blank\" rel=\"noopener\">deprecated since SQL Server 2012 for good reasons<\/a>), here\u2019s a tip: This is how <strong>a multi-victim-deadlock <\/strong>looks<strong> in Profiler<\/strong>:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-76bfc62c507fd7522d9c78183e1db70f\">\n.avia-image-container.av-av_image-76bfc62c507fd7522d9c78183e1db70f img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-76bfc62c507fd7522d9c78183e1db70f .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-76bfc62c507fd7522d9c78183e1db70f av-styling- avia-align-center  avia-builder-el-21  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2749 avia-img-lazy-loading-not-2749 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_MultiVictim_Deadlock_SQL_Profiler.png\" alt='' title='1503_MultiVictim_Deadlock_SQL_Profiler'  height=\"401\" width=\"754\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_MultiVictim_Deadlock_SQL_Profiler.png 754w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_MultiVictim_Deadlock_SQL_Profiler-600x319.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_MultiVictim_Deadlock_SQL_Profiler-300x160.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_MultiVictim_Deadlock_SQL_Profiler-705x375.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_MultiVictim_Deadlock_SQL_Profiler-450x239.png 450w\" sizes=\"(max-width: 754px) 100vw, 754px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p>Looks more like a \u201emulti-winner-deadlock\u201d \ud83d\ude09<\/p>\n<p>And this is how the XEvents GUI displays it:<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_image-90f4d04636a7797cc96e4cbd73c29f5e\">\n.avia-image-container.av-av_image-90f4d04636a7797cc96e4cbd73c29f5e img.avia_image{\nbox-shadow:none;\n}\n.avia-image-container.av-av_image-90f4d04636a7797cc96e4cbd73c29f5e .av-image-caption-overlay-center{\ncolor:#ffffff;\n}\n<\/style>\n<div  class='avia-image-container av-av_image-90f4d04636a7797cc96e4cbd73c29f5e av-styling- avia-align-center  avia-builder-el-23  el_after_av_textblock  el_before_av_textblock '   itemprop=\"image\" itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/ImageObject\" ><div class=\"avia-image-container-inner\"><div class=\"avia-image-overlay-wrap\"><img decoding=\"async\" class='wp-image-2751 avia-img-lazy-loading-not-2751 avia_image ' src=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_MultiVictim_Deadlock_XEvent_session.png\" alt='' title='1503_MultiVictim_Deadlock_XEvent_session'  height=\"332\" width=\"726\"  itemprop=\"thumbnailUrl\" srcset=\"https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_MultiVictim_Deadlock_XEvent_session.png 726w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_MultiVictim_Deadlock_XEvent_session-600x274.png 600w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_MultiVictim_Deadlock_XEvent_session-300x137.png 300w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_MultiVictim_Deadlock_XEvent_session-705x322.png 705w, https:\/\/andreas-wolter.com\/wp-content\/uploads\/2017\/09\/1503_MultiVictim_Deadlock_XEvent_session-450x206.png 450w\" sizes=\"(max-width: 726px) 100vw, 726px\" \/><\/div><\/div><\/div>\r\n\r\n<section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><p><br class=\"\u201cclear\u201c\" \/>HappyXEvent Parsing<\/p>\n<p>Andreas<\/p>\n<\/div><\/section>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_one_full-97c650ae075063b375f558a776c570f8\">\n#top .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n.flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nborder-radius:0px 0px 0px 0px;\npadding:0px 0px 0px 0px;\n}\n.responsive #top #wrap_all .flex_column.av-av_one_full-97c650ae075063b375f558a776c570f8{\nmargin-top:40px;\nmargin-bottom:40px;\n}\n<\/style>\n<div  class='flex_column av-av_one_full-97c650ae075063b375f558a776c570f8 av_one_full  avia-builder-el-25  el_after_av_textblock  el_before_av_hr  first flex_column_div av-zero-column-padding  column-top-margin'     ><section  class='av_textblock_section av-av_textblock-2de302bf1aa3cf4c9157dbe6f50ac7eb '   itemscope=\"itemscope\" itemtype=\"https:\/\/schema.org\/BlogPosting\" itemprop=\"blogPost\" ><div class='avia_textblock'  itemprop=\"text\" ><div><\/div>\n<div><\/div>\n<\/div><\/section><\/div>\r\n\r\n<div  class='hr av-av_hr-0ff602b3e980a3377077ff3c1c834df6 hr-default  avia-builder-el-27  el_after_av_one_full  el_before_av_social_share '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-social-sharing-box av-av_social_share-8644d330ffb238fff0cfa858c5295467 av-social-sharing-box-default  avia-builder-el-28  el_after_av_hr  el_before_av_hr  av-social-sharing-box-fullwidth'><div class=\"av-share-box\"><h5 class='av-share-link-description av-no-toc '>Share<\/h5><ul class=\"av-share-box-list noLightbox\"><li class='av-share-link av-social-link-facebook' ><a target=\"_blank\" aria-label=\"Share on Facebook\" href=\"https:\/\/www.facebook.com\/sharer.php?u=https:\/\/andreas-wolter.com\/en\/how-to-import-extended-events-session-event_file-target\/&#038;t=How%20to%20import%20Extended%20Events%20session%20event_file%20target%20and%20parse%20deadlock-graph\" aria-hidden=\"false\" data-av_icon=\"\ue8f3\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Facebook\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Facebook<\/span><\/a><\/li><li class='av-share-link av-social-link-twitter' ><a target=\"_blank\" aria-label=\"Share on Twitter\" href=\"https:\/\/twitter.com\/share?text=How%20to%20import%20Extended%20Events%20session%20event_file%20target%20and%20parse%20deadlock-graph&#038;url=https:\/\/andreas-wolter.com\/en\/?p=2754\" aria-hidden=\"false\" data-av_icon=\"\ue8f1\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on Twitter\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on Twitter<\/span><\/a><\/li><li class='av-share-link av-social-link-linkedin' ><a target=\"_blank\" aria-label=\"Share on LinkedIn\" href=\"https:\/\/linkedin.com\/shareArticle?mini=true&#038;title=How%20to%20import%20Extended%20Events%20session%20event_file%20target%20and%20parse%20deadlock-graph&#038;url=https:\/\/andreas-wolter.com\/en\/how-to-import-extended-events-session-event_file-target\/\" aria-hidden=\"false\" data-av_icon=\"\ue8fc\" data-av_iconfont=\"entypo-fontello\" title=\"\" data-avia-related-tooltip=\"Share on LinkedIn\" rel=\"noopener\"><span class='avia_hidden_link_text'>Share on LinkedIn<\/span><\/a><\/li><\/ul><\/div><\/div>\r\n\r\n\n<style type=\"text\/css\" data-created_by=\"avia_inline_auto\" id=\"style-css-av-av_hr-4474f20d2389e2e5ecf918a02da5132e\">\n#top .hr.hr-invisible.av-av_hr-4474f20d2389e2e5ecf918a02da5132e{\nheight:50px;\n}\n<\/style>\n<div  class='hr av-av_hr-4474f20d2389e2e5ecf918a02da5132e hr-invisible  avia-builder-el-29  el_after_av_social_share  el_before_av_comments_list '><span class='hr-inner '><span class=\"hr-inner-style\"><\/span><\/span><\/div>\r\n\r\n<div  class='av-buildercomment av-av_comments_list-88ce68e426f11248fa394058a3de040f  av-blog-meta-author-disabled av-blog-meta-html-info-disabled'><\/div>","protected":false},"excerpt":{"rendered":"This time it\u2019s about one of the things that Extended Events do better and more easily than all techniques before: Saving of deadlock-events that occurred and analyzing the respective deadlock-graph.","protected":false},"author":4,"featured_media":2751,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[55,42,56],"tags":[158,18,159,160,161,162,163,167,165],"class_list":["post-2754","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-extended-events-en","category-locking-blocking","category-tracing-monitoring-en","tag-deadlock-en","tag-extended-events-en","tag-multi-victim-deadlock-en","tag-parsing-en","tag-profiler_is_dead-en","tag-sql_deadlock-collector-en","tag-sql_trace_profiler-en","tag-system-health-en","tag-xquery-en"],"_links":{"self":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2754","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/comments?post=2754"}],"version-history":[{"count":7,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2754\/revisions"}],"predecessor-version":[{"id":3013,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/posts\/2754\/revisions\/3013"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media\/2751"}],"wp:attachment":[{"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/media?parent=2754"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/categories?post=2754"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/andreas-wolter.com\/en\/wp-json\/wp\/v2\/tags?post=2754"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}