![]() ![]() For example: If the isolation level is serializable then that could very well be the reason for the deadlock. The main thing that I find of interest in the process list which you cannot see on the graphical view of the graph is the isolation level of the process. These are SET options such as ANSI_NULLS or QUOTED_IDENTIFIER The maximum time a process can wait for a lock to be released The host name on which the client is runningĭatabase ID of the database on which the process was run The client application that created the process The timestamp of the processing of the last attention event such as a timeout or a cancellation on the thread involved in the deadlock. The number of transactions involved in the deadlock If not executed in parallel this value will be 0. This is the execution context when the process is executed in parallel. The ID of the processor on which the process is running The type of lock the process wants to take The date time the last transaction started The name of the transaction or the transaction type: I.e. It seems MSDN is referring to the details available only in the graphical representation of the deadlock graph. If you look at the XML itself, you will notice a couple of other details not mentioned in MSDN. In other words the SQL script that is being executed will be shown here. This describes the event type and the code that is executed. This value corresponds to the request_owner_id field in the sys.dm_tran_locks DMV. This displays the transaction id of the transaction which owns the process. The amount of time the process waited for the resource to become available The part in brackets is the hash value for the index key The third part: 72057594045595648 is the Heap or B tree ID The first part: KEY indicates the resource type. The amount of transaction log used by the process The following details are displayed for each process per MSDN: The process list contains one node for each process involved in the deadlock. In this particular instance there is only one victim, but it is possible to have multiple victims involved in a deadlock.įigure 5: The victim list The process list The victim list is exactly what it says it’s a list of process ids which have been selected to be the victims of the deadlock. We will now look at each of these in detail. The Deadlock XML consists out of 3 sections: If you extracted your deadlock using Extended Events you can double click on the xml_report line in the event details which will open the xml description as an. xdl file in a text viewer such as notepadĬhange the file extension from. The XML contains information which is not visible in the graphical representation of the Deadlock Graph, which makes it necessary for us to look at the XML description.Įxtracting the XML Description from the Deadlock Graphĭepending on how you captured your Deadlock Graph you have multiple ways of viewing the XML text. In this article, I will look at what information is available in the XML description of the Deadlock Graph. This method for doing this varies depending on how you’ve configured the event, and there are tonnes of examples out there.In my previous 2 articles, What is a SQL Server Deadlock and Understanding the graphical representation of the SQL Server Deadlock Graph, I discussed what a deadlock is, how SQL Server handles deadlocks, some tips on how to reduce deadlocks and ultimately what information you can glean by just looking at the graphical representation of the Deadlock Graph. To extract the information by script, you need to query the event session data. It also explains how to similate a deadlock and then extract the information captured via the GUI (just be sure to start your session before simulating the deadlock). This article by Eduardo Pivaral over on gives a good example of how to create an Extended Events Deadlock session using both the SSMS GUI, and script. To focus only on deadlocks and the deadlock chain, you’ll need to create a specific event session that focusses only on the required events. This session captures more than just deadlocks however, so you’ll need to query the captured data to extract only the deadlocks. The first is the built-in system_health session (enabled by default). You have two options for getting information about deadlocks from Extended Events. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |