Deadlocks can cause a lot of pain for data professionals. Fear not, deadlock detection and analysis with SentryOne 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.
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 SentryOne 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.
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.
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.
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, “SentryOne 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.
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.
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 SentryOne 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 SentryOne, 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 SentryOne Portal
As of SQL Sentry version 2020.8.7, we can also view both blocking and deadlocks within SentryOne Portal. If you are making use of SentryOne Portal, it’s another way you can access and share deadlock analysis from SQL Sentry.
Viewing deadlocks in SentryOne Portal
More information about the deadlocks functionality in SentryOne Portal can be found in Tyler Benfield’s blog post, “Introducing SQL Server Blocking and Deadlocks Views in the SentryOne 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 SentryOne? 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.
