Analyzing Deadlocks

Deadlocks can cause a lot of pain for data professionals. Fear not, deadlock detection and analysis with SQL Sentry is straightforward. Having visibility into deadlocks that are occurring in your environment and information about the deadlock event itself is quite important. Let’s look at the features available for dealing with deadlocks, starting with alerting for deadlocks.

The SQL Server: Deadlock Condition

Configuring the SQL Server: Deadlock condition enables notifications about deadlock events that occur within your environment without having to constantly check SQL Sentry. In the screenshot below, you can see I have configured the Send Email action to fire when the SQL Server: Deadlock condition has been triggered.

Analyzing Deadlocks in SQL Sentry - Figure 1

SQL Server: Deadlock condition—General Conditions

The notification email includes links that make it easy to open the deadlock in SQL Sentry so that we can begin troubleshooting the issue right away. (Note that this functionality requires SQL Sentry to be installed on the machine on which we are opening the email.)

If there are specific deadlocks you are aware of in your environment, you can filter out certain applications through Condition Settings. More information about alert tuning can be found here. If you have any further questions about alerting in SQL Sentry, your SQL Sentry Customer Success Manager would be happy to schedule a technical call with the team.

Event Calendar—Deadlocks

As I mentioned previously, it’s important to know how often deadlocks are occurring on a specific instance or in the monitored environment. To view them, we can either open the Event Calendar at an instance level and then filter for Deadlocks or open the Event Calendar for Deadlocks by selecting the Deadlocks option under the instance.

Analyzing Deadlocks in SQL Sentry - Figure 2

Opening the Event Calendar at a target level and filtering by Deadlocks

With this information, we can become increasingly aware of potential issues that might arise or confirm that work we are doing to minimize the amount of deadlocking is working as expected.

By opening our Event Calendar (filtered by Deadlocks automatically), we can see this information.

Analyzing Deadlocks in SQL Sentry- Figure 3

When you first open the calendar, you will be looking at the current day by default

Above the Event Calendar scrolling from left to right, we can change the date and time we are currently viewing and change the view from 1 minute to the full day. By changing the number of days we are viewing, we can easily see how often deadlocks are occurring on this target machine.

Analyzing Deadlocks in SQL Sentry - Figure 4

Viewing the Event Calendar for multiple days to identify patterns in relation to deadlocks

Right-clicking any deadlock event will enable us to select Deadlocks from the Jump To menu.

For more information about the Event Calendar, check out Patrick Kelley’s blog post, “SQL Sentry Event Calendar Tips and Tricks.”

The Deadlocks Tab

The Deadlocks tab provides details about the processes and resources involved in the deadlock event, with a visual representation in the second section of the screen. If our hands are tied in relation to troubleshooting further, we can export this information and send it onto the relevant team or vendor. By selecting File > Export Data, we can export the information shown in the top section to Excel. By right-clicking the bottom section, we can export the Deadlock XML. We can also make use of built-in reports under Reports > Performance Analysis > Deadlocks.

Analyzing Deadlocks in SQL Sentry_Figure5

Deadlock event captured in SQL Sentry

In the grid view, we can see processes that have obtained the lock under the Owners heading. Processes waiting to obtain the lock can be viewed under Waiters. Selecting a process in the grid view will in turn highlight this process in our graphical representation. This also works when highlighting the SPIDs in our graphical representation. By right-clicking a process, we can use the Jump To functionality to quickly access other tabs to display more information for the same time period.

Analyzing Deadlocks in SQL Sentry_Figure6

Jump To functionality within the SQL Sentry Deadlocks tab

Jumping to the Dashboard will highlight the runtime of this query on each of our graphs. By right-clicking, we can also select Show Plan, which will open SQL Sentry Plan Explorer for the selected query.

The graphical representation of the deadlock displays the order of events in the deadlock (numbers) and the requested lock mode (letters). We can also easily identify the deadlock victim, as it is highlighted in red. For more complex deadlocks than that shown above, the playback functionality can be extremely useful.

If you would like other people in your organization to take advantage of many of the above features when exporting this information from SQL Sentry, we recommend they have Plan Explorer installed on their local workstation.

For more information about the deadlock playback functionality, see Aaron Bertrand’s post, “New Deadlock Visualizations in SQL Sentry and Plan Explorer.”

The Deadlocks View in SQL Sentry Portal

As of SQL Sentry version 2020.8.7, we can also view both blocking and deadlocks within SQL Sentry Portal. If you are making use of SQL Sentry Portal, it’s another way you can access and share deadlock analysis from SQL Sentry.

Analyzing Deadlocks in SQL Sentry_Figure7

Viewing deadlocks in SQL Sentry Portal

More information about the deadlocks functionality in SQL Sentry Portal can be found in Tyler Benfield’s blog post, “Introducing SQL Server Blocking and Deadlocks Views in the SQL Sentry Portal.”

Conclusion

I hope this blog post helps you navigate SQL Sentry’s ability to identify and troubleshoot deadlock events. Stay tuned for more tips and tricks for using SQL Sentry to optimize database performance.

  • New to SQL Sentry? Download a free SQL Sentry trial to see for yourself how SQL Sentry can help you get to the root of problems and achieve peak database performance.
  • New to Plan Explorer? Check out our free demo kit, which can help you get started with this free query analysis and optimization tool.
Thwack - Symbolize TM, R, and C