This file contains the code to create such a specialized Extended Event Session. You will find attached to this article a file called: In order to properly choose our source, let’s first review what has to be done for each option.Ĭreating an Extended Event dedicated to deadlock monitoring If we choose system_health Extended Events though, there are some additional tasks that may be necessary to get something equivalent to a homemade Extended Event. We can either use a built-in Extended Events called system_health or create a specific one. These questions will find answers in following sections.Ĭhoosing source of our initial data collection How will we automate things? Will we use a SQL Agent Job or a Windows Scheduled Task? Which tables should be used as input or output of which part of the process? Which data source will be used system_health or homemade? And in what configuration? This means that we need to make choices on different aspects: Now, it’s time to automate the first two steps of the above process. You can download the code of this procedure (and for related objects) from the following link, or at the end of third article of this series. The „Extract” step is implemented in a stored procedure called Monitoring.CollectDeadlockInformation while the “Transform/Shred” step is implemented in the Reporting.ShredDeadlockHistoryTbl stored procedure. In the previous article, we defined and implemented (as dynamically as possible) a process for deadlock handling based on Extended Events consisting into three steps that were: How to use SQL Server Extended Events to parse a Deadlock XML and generate statistical reports.We’ve also seen that we could generate a nice timeline of their occurrences over time for a given time period. This information takes the form of an XML description of the deadlock events. In the next article, we talked about data collection procedures that can be used to store information about deadlocks into a table from either Error Log and Extended Events.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |