Next we use the nodes() method of the XML variable to extract all event elements with an event name of xml_deadlock_report.This simple operation can speed up XML parsing tremendously First we copy data from the ring buffer directly into an XML variable.target_name = 'ring_buffer' - Get the time stamp and deadlock graph SELECT xed. dm_xe_session_targets xet INNER JOIN sys. Here is a simple script to grab deadlock graphs:ĭECLARE system_health XML - Copy ring buffer into an XML variable SELECT system_health = CAST ( xet. To learn more about this process, check the resources section at the end of this post for a great article from Jonathan Kehayias. If you were going to set something up to grab deadlock graphs on a regular basis in a production environment I would recommend, without hesitation, that you do the work of querying the file target. This isn't always the best approach, but for our purposes it will work. To keep things simple we are going to use the ring buffer target for our queries. Writing queries to get data out of extended events sessions can get complicated fast. You'll be surprised how much information is available. There is a lot of documentation out there on system health, so find something interesting in yours and google it. I highly recommend taking a look around at some other events and see what you can find. Below you can see how I would set up a filter to just show deadlocks (this test system only has a single deadlock): To set up a filter just click on the 'Filters.' button in the toolbar at the top of the window. When looking at your system health session using SSMS, make sure you also take advantage of it's filtering capabilites, you can filter on both the timestamp and event name. If you now click on the 'Deadlock' tab, you can see the graphical representation of the deadlock: In this case we found a deadlock graph, and as you can see the beginning of the deadlock XML is shown. The name column contains the name of the event type that was captured, and the timestamp tells you when it happened, single-clicking on any of these events will show more detail at the bottom of the window: Once it opens up you'll see the default layout, which include two columns name and timestamp. To view the session data, double-click on package0.event_file. Getting there is pretty simple, once connected to an instance via the object explorer, just go to Management > Extended Events > Sessions > system_health: Viewing Deadlocks in System Healthīefore we query system health for deadlocks, let's take a look at it in SSMS. There's a lot more information available in this XE session, but for now we are just going to focus on one: deadlocks. Do you have some long-waiting tasks? Check system health. The system health XE session has an amazing amount of information in it. Thankfully SQL Server ships with the handy system_health extended event (XE going forward) session already set up and running. Scrolling through the available event list alone can be a little overwhelming. You have to choose which events you want to capture, how you want to store it, and how you want to filter it. System HealthĮxtended Events can be intimidating to start working with. Tag(s): database administration, monitoring, tsql tuesday, m82labs archiveīefore we dive into some extended events and PowerShell fun I want to say thanks to Jes Borland ( WWW/ Twitter) for hosting this months T-SQL Tuesday! If you are interested in learning more about T-SQL Tuesday, take a look at Jes's post, and check out the #tsqltuesday hashtag on Twitter.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |