We can use the following query to extract information from the ring buffer target. We should be familiar with the script as well so that we can quickly retrieve information for the deadlock from the system_health event session.Įxtract SQL Server Deadlock information using T-SQL from the ring buffer target We can query an extended event file as well to get the deadlock information. We learned to get the deadlock information using the system_health extended events in a GUI mode. These graphs come in handy when it is difficult for DBAs to understand the XML deadlock information. Click on the Deadlock tab, and it shows the following deadlock graph for my demoĭeadlock graph is an excellent way to interpret the deadlock related information quickly and effectively. It gives you complete information about the transactions, locks, deadlock victim and statementsĭeadlock Graph: Many times, we want to view the deadlock graph to get the details quickly in a graphical mode. XML Deadlock Report: You can double click on the XML report value, and it opens the complete XML report in the new query window. You get both the XML deadlock report and the SQL Server deadlock graph using this extended event. It shows the deadlocks occurred for the SQL instance. We are interested in this particular filter only for this demo. You can create additional filters such as date range. In the event file viewer, right-click and set the filter to display the xml_deadlock_report. Double click on package0_event_file to open the extended event file. We will use the ring buffer as well to view the deadlock information. This extended event session has two targets. It is like a black box recorder that captures useful information and helps to troubleshoot the issues in SQL Server. You can find the system_health extended event session in SQL Server. In the article, Max Worker Threads for SQL Server Always on Availability Group databases, we explored the use of system_health extended event session for the SQL Server Always On Availability Group databases. SQL Server Deadlock monitoring using the system_health extended event Let’s look at another way of monitoring the deadlock in SQL Server without enabling the trace flags as well. Let’s enable these trace flags and simulate a SQL Server deadlock situation. Out of these two trace flags, the useful and recommended trace flag is 1222 as it returns the resources, their lock and deadlock information in an XML format. Usually, DBA enables the trace flag 12 to capture the deadlock information in the SQL Server error logs. It is essential to monitor this deadlock situation. The killed process is known as the Deadlock victim. SQL Server automatically monitors the deadlocks every 5 seconds and kills a session so that another transaction can complete the work. This thread also needs to wait for the other transactions to complete User A also requires to read data from the Invoice Details page to complete the transaction, but that page has an Exclusive lock by the user B. It has to wait until the User A releases the Exclusive lock on the page It tries to place an Intent Shared lock on the Invoice table and shared lock on the page to read the data. User B wants to read the same data user A wants to update. User A also requires an Exclusive lock (X) on the row of the Invoice table User A: Intent Exclusive (IX) lock on the Invoice table and page it requires to modify We can further understand the SQL Server deadlock scenario using the following image. Both the threads cannot process due to insufficient resources. In the following image, we can see that thread A is waiting for the resources held by thread B, and similarly, thread B is waiting for the resources held by thread A. If two processes in SQL Server wants to have exclusive access to a resource held by the other process, it is called a deadlock situation. It is normal behavior and helps to ensure the ACID properties (Atomicity- Consistency – Isolation – Durability). SQL Server obtains locks on required resources to perform specific tasks such as Select, Insert, Update, Delete. Later, we will cover how we can monitor deadlocks using the system_health extended event. Let’s have a quick overview of “Deadlocks in SQL Server”. You can also go through these articles: SQL Server deadlock definition and Overview and What are SQL Server deadlocks and how to monitor them, to understand deadlock in SQL Server. You might face frequent deadlocks issues, and they have a direct impact on the application performance. Performance issues act like an open stage, and you need to look at every aspect such as CPU, RAM, server performance, database performance, indexes, blocking, waits, and SQL Server deadlocks. You should ensure that the database performance is optimal all the time without any impact on the databases. Performance monitoring is a must to do the task for a DBA.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |