Detect and Resolve SQL Deadlocks with Performance Advisor and Plan Explorer PRO

Detect and capture deadlocks with SentryOne Performance Advisor and Plan Explorer PRO to resolve deadlocks faster and improve performance.

Get more information about capturing and detecting deadlocks with Performance Advisor and resolving them with Plan Explorer PRO.

Let's get started

SQL Deadlock

“One of my favorite things about Plan Explorer is the ability to copy the plan's XML and paste it in. When I'm working in client environments and they don't have PE installed, it's incredibly helpful.”

Erin Stellato

What is a SQL Deadlock?

A SQL deadlock arises when two or more tasks block one another by both having a lock on a resource which the other tasks are attempting to lock. There are a few different resources that can lead to a SQL Server deadlock such as locks and memory.

Locks

When resources such as objects, pages, metadata, rows or applications are waiting to acquire locks, which can cause a SQL deadlock.

Memory

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 20 MB of memory available and two queries each requires 20 MB, then both queries have to wait for the other to release memory.

Detecting SQL Server Deadlocks

Detecting and capturing SQL deadlocks with Performance Advisor is very 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 still see all other relevant details, such as the statements that were executing 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.

There is no need to translate SPIDs or reference IDs as the host, application and resource names are all labeled in the graph. Easily see the SQL Server deadlock victim shown in red, as well as lock types, all in the order they were applied. With this functionality, you can journey right through the deadlock and get to the root of the cause to resolve it more efficiently.

Once you have captured the SQL deadlocks in Performance Advisor, you can simply export the .xdl files that can be shared and opened in Plan Explorer PRO 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.

LOOKS LIKE YOUR BROWSER IS OUT OF DATE

For your convenience here are links to supported browsers:

Your browser could be incompatible with this website.
If you close this window and continue you may experience less than optimal behavior.

Don't show this again