![]() Hostname is the machine name of the application which is requesting the query. The Process ID which is required to uniquely identify each process. The columns which we be getting will be under the following node so we start reading XML from that particular node “event/data/value/deadlock/process-list/process”. We will parse the document using the system stored procedure and will get the following columns from the deadlock graph XML. You can get detailed information about the system stored procedure on MSDN here. There is a Microsoft SQL Server Stored Procedure for that and it’s pretty handy, i.e. After that, we need to parse the XML so that we can read it easily. This query will give you only the deadlocks captured.The script is pretty simple and also has comments so I won’t go into each and every detail here and will not describe everything but some important points should be mentioned here.įirst we mentioned earlier we get the deadlock graph which is by default in an XML Format. You can get detailed information about the system view “sys.fn_xe_file_target_read_file” from MSDN here. So, we will be querying a system view “sys.fn_xe_file_target_read_file” to query the extended event trace “system_health” which is already running by default so you don’t have to setup anything. The deadlocks are also captured in the system trace extended event with the object name (a column in the system trace) as “xml_deadlock_report”. Please have a look at this event on MSDN here.Īs of now, we are only concerned about the deadlocks. There have a lot of useful detailed information which is already being captured. Extended events capture a lot of data from the system and you should explore that as well. So let’s start and discuss extended events features which I will be using. It was already inside the SQL Server just out of the box (not the query but the product features I am using). ![]() That’s all, you have the solution right then and there. ![]() Yes! If you are using Microsoft SQL Server 2008 or later then you don’t have to do anything else just understand what I will explain here and use the script. ![]() Here, I would like to give you about a small, simple yet a powerful way of using SQL Server Extended Events and monitoring SQL Server Deadlocks.Īs we all are aware of deadlocks and ideally speaking we would never want to hear about it in our production environments but deadlocks do happen and we do want to get in the Root Cause Analyses of the deadlocks.īefore going into the details of my simple script which I will discuss in detail very shortly, I want to let you know about the any prerequisite for the script to run, there are NONE. Though, Extended Events is a way more complex and detailed topic which I cannot cover in just one article I will discuss one of its very good utilities out of the countless. Some people even don’t know about the features which are already in the SQL Server and they have not used it, like SQL Server Extended Events. SQL Server is a very powerful tool and wherever I go, I see the tool being way much underutilized. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |