A SQL deadlock arises when two or more tasks block one another by both having a lock on a resource that the other tasks are attempting to lock. There are a few different resources that can lead to a deadlock, such as locks and memory.
When resources such as objects, pages, metadata, rows, or applications are waiting to acquire locks, which can cause a SQL deadlock.
Sometimes simultaneous requests are waiting for memory grants that can’t be fulfilled with the memory that is available. Requests that are waiting for memory can also lead to a SQL Server deadlock. For instance, if you only have 20MB of memory available and two queries each requires 20MB, then both queries have to wait for the other to release memory.
“One of my favorite things about Plan Explorer is the ability to copy the plan's XML and paste it in.”
- Erin Stellato
Detecting and capturing SQL deadlocks with SentryOne is straightforward. The Deadlocks tab presents a complete view of deadlocks in SQL Server. See all of the processes and resources involved in a deadlock with a visual representation. Going beyond the easy-to-read graphical view, you can see all other relevant details, such as the statements that were executed while the deadlock was being detected. There is no need to install any agents or set any trace flags. You also don’t need to perform any particular configuration of the monitored servers.
With SentryOne SQL Sentry, there is no need to translate SPIDs or reference IDs as the host, application, and resource names are all labeled in the graph. Instead, you can easily see the SQL deadlock victim
Once you have captured the SQL deadlocks in SQL Sentry, you can simply export the .xdl files that can be shared and opened in SentryOne Plan Explorer just like a plan file. You can view the deadlock file in the deadlock grid view, where each file is expandable so that you can see the details of the processes that participated in the deadlock. You can also use the deadlock graph, where the victim is highlighted in red, and view the process and resource nodes as well as any relationships that occur between them.
SentryOne SQL Sentry 18.4 and SentryOne Plan Explorer 18.3 provide new SQL Server deadlock visualization enhancements, including the ability to replay a deadlock to view a simulation of how it happened in real time, a more intuitive deadlock diagram, zoom
Product Manager Aaron Bertrand walks through the new SQL Server deadlock visualization options in SentryOne Plan Explorer and SentryOne SQL Sentry that provide more intuitive deadlock analysis.
SentryOne Training Manager Lori Edwards walks you through how to replay a deadlock to view a simulation of how it happened in real time, the zoom and auto-fit features, alternative graphical layouts, and a more intuitive deadlock diagram.
Get more information about capturing and detecting SQL Server deadlocks with SentryOne and resolving them with SentryOne Plan Explorer.