![]() ![]() Hence, this would not be easy to read and average size of a row could be very high. Furthermore, if we keep one row per deadlock and 10 process properties, we would get a table with at least 21 columns (deadlock identifier and twice the ten properties). This means we will take the same kind of information for any of them. First, let’s recall that there are at least two processes implied in deadlocks. There are actually some facts that we need to consider. But we need to keep only those which would be useful for reporting purposes! Let’s start to think about the design of destination table… So, there are plenty of properties that we could extract from the XML graph. ![]() Here is an example of its contents corresponding to current example.ĭesigning Deadlock XML transformation process output table The resource-list which lists database resources used by processes when deadlock situation occurred. Each process sub-section has a wealth of information about it: The process-list which, as we could expect, lists the processes implied in deadlock situation. The victim-list which actually only tells which process in next XML section has been killed by Deadlock Monitoring Thread. Here is an example.Īs we can notice, it’s divided into three XML sections: But, let’s first review what a deadlock XML graph looks like. Now that we have extracted deadlocks from Extended Events and assigned a unique identifier to each record, we can take the product of this extraction and split up its XML contents. As soon as we designed the “Transform/Shred” operation, we will talk about how to automate both “Extract” and “Transform/Shred” steps using a single SQL Server Agent job. Therefore, we won’t discuss a lot about it in following sub-sections and instead focus on deadlock XML processing. The first step of this process, “Extract”, has been discussed in the previous article entitled How to report on SQL Server deadlock occurrences. We could extract XML graphs every hour and transform only once a day while reporting can be performed at any time by authorized users. Another advantage of this design is that we could change the extract method (we could use an extraction from SQL Server Error Log), remaining steps would be kept intact.Įach step of this process can be run asynchronously and uses the results of its predecessor to generate its output. So, we will divide the process into 3 steps as shown in following figure. This process could be built in one piece of code: we could do all the job inside a single stored procedure, like we did to extract deadlock occurrence time from SQL Server Error Log in the previous article, but the complexity of such a procedure would be very high. Now, let’s focus on a process for collecting and transforming XML version of deadlock graphs into something we can use for reporting, in short, something on which it’s easier to compute statistics. These columns will then be available to generate reports as we discussed just above.ĭeadlocks data processing using SQL Server Extended Events This article will define a process that will collect these XML descriptors into a base table and split them into a series of columns. These reports would be especially useful when we don’t always have the code of the application that generates these deadlocks and we must provide suitable information to the author to inform (and sometimes try to convince) them that it can be the root cause of performance problems then help him in solving these problems. This would allow us the ability to generate reports based on deadlock properties like the application name, queries implied in deadlocks… This means that we can do so much more than just store this XML. Now, it’s time to go deeper in the processing of this deadlock information…Īs we collect this information in XML format, we can query the XML contents directly in SQL Server using the XQuery feature of Transact-SQL. We also discussed a simple report which leads to the creation of a deadlock occurrences timeline. In previous article entitled “ How to report on SQL Server deadlock occurrences“, we’ve seen how to extract deadlock information from either system_health or specialized Extended Event and store this information into a table in a SQL Server database. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |