If you’ve ever had a SQL Server database slow to a crawl, queries timing out for no obvious reason, and end users flooding your support line, you’ve probably wondered if a deadlock is to blame. That’s why learning how to find the deadlock in SQL Server is one of the most critical skills for any database admin or backend developer working with Microsoft’s relational database system. I’ve worked with SQL Server instances for over 8 years, and I can count on one hand the number of performance outages I’ve seen that weren’t either deadlocks or missing indexes. It’s that common, and most people waste hours guessing instead of using the built-in tools that do most of the work for you.
How to Find the Deadlock in SQL Server Using Built-in System Tools
You don’t need expensive third-party monitoring software to track down deadlocks, because SQL Server comes with all the tools you need pre-installed. The first and most recommended tool for modern SQL Server versions is Extended Events, a lightweight tracing system that replaced the old SQL Server Profiler years ago. Extended Events are lightweight, so they won’t drag down your production server performance even when running during peak traffic hours. You can set up a custom Extended Event session specifically to capture deadlock data as it happens, and filter it to only collect relevant information so you don’t have to sift through thousands of unrelated logs.
If you don’t have time to set up a custom session, you can use the System Health Session, which is enabled by default on every SQL Server instance from 2012 onwards. The system health session stores deadlock data in XEL files, which you can access directly through SQL Server Management Studio (SSMS) without any extra configuration. It typically retains data for the last 3 to 5 days, depending on your server activity, so it’s perfect for investigating deadlocks that happened recently. This is the most reliable method when you want to know how to find the deadlock in SQL Server that happened a few days ago, without having planned for it in advance.
When you pull deadlock data from either tool, you’ll get a deadlock graph, which is a visual or XML representation of the conflicting transactions. The graph will show you which transactions are involved, what locks each is holding, what locks each is waiting for, and which transaction SQL Server chose to kill as the deadlock victim to resolve the impasse. You don’t need to be an XML expert to read it, either: SSMS will render the graph as a simple diagram if you open the XEL file directly.
Common Signals That Point to an Unresolved SQL Server Deadlock
You don’t have to run deadlock checks every hour to catch issues. There are clear warning signs that will tell you when you need to start investigating for deadlocks, and these are easy to track even if you don’t have a full monitoring stack set up. The most common signals include:
- Consistently timed out queries that run fine when executed manually outside of peak traffic hours
- Lock wait times that spike 200% or more above your baseline for no obvious reason, like a sudden large data import
- Error 1205 messages in your SQL Server error log that explicitly reference deadlocked transactions
- Application logs showing repeated "transaction was deadlocked" errors with no corresponding hardware or network issues
You shouldn't assume every slowdown is a deadlock, but these signs mean you should prioritize checking for them first before digging into application code or server hardware. I’ve seen teams spend three days troubleshooting a new feature rollout when a 2-minute check of the SQL Server error log for 1205 errors would have pointed them straight to a deadlock between two existing nightly jobs that happened to overlap after a minor schedule change. It’s easy to overcomplicate the problem, so start with the simplest checks first.
How to Differentiate Deadlocks From Regular Blocking Issues
A lot of new admins mix up deadlocks and regular blocking, and that can lead to wasted time troubleshooting the wrong problem. Regular blocking is completely normal in any relational database: one transaction holds a lock on a row or table, another transaction needs that same lock, so it waits until the first transaction finishes and releases the lock. Most blocking resolves itself in milliseconds or seconds, and you’ll barely notice it unless you’re looking at fine-grained performance metrics.
A deadlock is a specific type of permanent blocking that can never resolve on its own. It happens when two or more transactions each hold a lock that the other transaction needs, and neither will release their existing lock before getting the one they’re waiting for. It’s a classic catch-22, and SQL Server has to step in and kill one of the transactions (the one with the lowest rollback cost, by default) to break the cycle. Regular blocking will resolve on its own if you wait long enough, but deadlocks will stay stuck until one transaction is terminated, either automatically by SQL Server or manually by an admin.
You can tell the difference by running the sp_who2 stored procedure, which shows you all active sessions and their blocking status. If you see a blocking chain that doesn’t move for 5+ minutes, and the sessions at the head of the chain are showing as sleeping or waiting, that’s almost certainly a deadlock, not regular blocking. Keep in mind that long-running regular blocking can turn into a deadlock if more transactions join the chain, so it’s good to fix long-running queries even if they aren’t causing deadlocks yet.
Best Practices to Streamline Deadlock Detection and Resolution
Once you know how to find deadlocks in SQL Server, you can make the process even faster with a few simple best practices that take almost no time to set up. First, never turn off the default System Health Session to save disk space. The XEL files it generates are tiny, roll over automatically, and the storage cost is negligible compared to the time you’ll save when you need to investigate a deadlock that happened over the weekend.
Set up custom alerts for deadlock error 1205 so you get notified as soon as a deadlock occurs, instead of waiting for end users to report performance issues. You can configure these alerts to send you an email or a Slack message, and they only trigger when a deadlock is detected, so you won’t get spammed with irrelevant notifications. This is especially useful for production servers, where even a 10-minute delay in resolving a deadlock can cost you thousands of dollars in lost revenue.
Always save deadlock graphs when you find them, and store them in a shared folder that your whole team can access. They make it much easier to trace the root cause of a deadlock, whether it’s missing nonclustered indexes, poorly ordered queries in your application code, or overly restrictive transaction isolation levels. Don’t just kill the victim transaction and move on, either. If you don’t fix the underlying cause, the same deadlock will happen again the next time those two transactions run at the same time.
One common mistake I see new admins make is adjusting the deadlock priority for critical transactions to make sure they never get chosen as the victim. That doesn’t fix the deadlock, it just shifts the negative impact to less critical transactions. You’re better off fixing the root cause of the deadlock than playing whack-a-mole with victim priorities.
At the end of the day, learning how to find the deadlock in SQL Server doesn’t require expensive monitoring tools or years of advanced database experience. All the tools you need are built right into the platform, and following the steps we outlined will help you catch and resolve deadlocks long before they impact end users. Even if you only run small SQL Server instances for internal applications, adding deadlock detection to your regular maintenance routine will save you hours of frustrating troubleshooting down the line.