![]() ![]() The deadlock graph is the easiest way to get information about a deadlock. Information on the process waiting on the resource, lock type requested and the request type.Ī resource node will be included for each resource involved in the deadlock. Information about the owner of the process and corresponding lock mode The id of the object on which deadlock occurred, mostly the same as the HoBTID. This is the ID or name of the index involved in the deadlock The database id on which the resource exists ![]() The following information is available in the resource list: In this example, the deadlock involved Keys or rather indexes. A resource can be a row, a key, a page, an extent, a heap or a B-tree (HoBT), a table, a file, an allocation unit or metadata. The resource list provides detailed information about each resource involved in the deadlock. You then have to go back and determine if this isolation level is required and if so, review all queries involved to ensure that they are always accessing tables in the same order.įigure 6: The Process List The resource list 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. 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |