If you’ve ever spent hours troubleshooting a slow SQL Server instance where users keep reporting transaction timeouts and failed operations, deadlocks are likely one of the first culprits you’ll want to rule out. These circular lock conflicts happen when two or more transactions hold locks the others need, and neither can proceed, forcing SQL Server to terminate one transaction to break the cycle. In this guide, we’ll walk through how to check the deadlock in SQL Server using only native tools, no expensive third-party software required.
Built-in Tools You Can Use to Check the Deadlock in SQL Server
SQL Server comes with several native tools out of the box, so you don’t have to install anything extra to track down deadlocks. The right tool for you depends on whether you’re looking for recent deadlocks or want to capture new ones as they happen, and whether you’re working on a production or development instance.
If you're trying to check the deadlock in SQL Server for a production instance, you never want to use the old SQL Server Profiler tool, as it adds significant overhead that can make performance issues even worse. Profiler has been deprecated for years, and Microsoft recommends using Extended Events instead for all tracing needs.
The most reliable tools for deadlock detection are the built-in System Health Session, custom Extended Events sessions, and the lightweight Activity Monitor for quick real-time checks. Trace flag 1222 is another option if you want deadlock details written directly to your SQL Server error log, but it’s less user-friendly than the graphical deadlock graphs you get from Extended Events.
Step-by-Step Deadlock Check Using the System Health Session
The System Health Session is an Extended Events session that’s enabled by default on every SQL Server instance starting from 2012, and it automatically captures deadlock events as they happen, along with other critical performance data. This method is the fastest way to check the deadlock in SQL Server for recent incidents, as you don't need to set up any custom tracing ahead of time.
To access deadlock data from the System Health Session, open SQL Server Management Studio (SSMS) and connect to your target instance. Navigate to Management > Extended Events > Sessions > systemhealth, right-click the session, and select “Watch Live Data” if you want to capture deadlocks as they occur in real time. For past deadlocks, right-click the “package0.eventfile” node under the system_health session and select “View Target Data” to see all saved events.
You can filter the event list to only show deadlock events by typing “deadlock” in the event name filter bar. Each deadlock event includes a graphical deadlock graph that you can open by clicking the Deadlock tab in the event details pane. It’s normal to see a small number of deadlocks on busy instances, but if you’re seeing more than a handful per day, you’ll want to dig into the root causes.
How to Interpret Deadlock Results to Fix Root Causes
Looking at a deadlock graph for the first time can feel overwhelming, but it’s actually very straightforward once you know what to look for. The graph is designed to show you exactly what caused the circular lock conflict, so you don’t have to guess which queries or processes are to blame.
There are three core parts of every deadlock graph you’ll want to pay attention to:
- Victim process: The transaction that SQL Server chose to roll back to resolve the deadlock, marked with a blue X icon. This process has a lower estimated rollback cost than the other conflicting processes, so it’s the one that gets terminated to free up locks.
- Resource list: The exact locks each process was holding when the deadlock occurred. You’ll see the database ID, object name, and lock type (shared, exclusive, update) for each locked resource, so you can pinpoint exactly which tables or rows are causing the conflict.
- Process nodes: These show the full details of each conflicting transaction, including the exact SQL query that was running, the user account that executed it, and the client application that triggered the request.
That means you don’t have to sift through hundreds of lines of logs to find the problem. If you see the same two queries popping up in deadlock events repeatedly, those are the first places you’ll want to make changes. For example, if you see one query updating a users table then an orders table, and another query updating the orders table then the users table, that’s a classic circular access pattern that’s easy to fix.
Pro Tips to Avoid Frequent Deadlock Occurrences
Detecting deadlocks is only half the battle – you’ll also want to make changes to prevent them from happening again in the future. I’ve worked with hundreds of SQL Server instances over the years, and most recurring deadlocks can be fixed with a few simple changes, no major overhauls required.
Keep transactions as short as possible. The longer a transaction runs, the longer it holds locks, and the higher the chance it will conflict with another transaction. Never wrap user input waits, external API calls, or long-running processing steps inside a database transaction – commit your data changes as soon as they’re complete to free up locks faster.
Standardize table access order across all your application queries. Deadlocks happen when two transactions access the same set of tables in opposite order. If you enforce a rule that every transaction accesses tables in the same order (for example, always users first, then orders, then payments), you eliminate the circular wait condition that causes deadlocks entirely.
Use the lowest possible transaction isolation level for your workload. The default Read Committed isolation level works for most use cases, but switching to Read Committed Snapshot Isolation (RCSI) can cut down deadlock risk drastically. RCSI uses row versioning instead of shared locks for read operations, so read queries don’t block write queries and vice versa.
You can also add missing indexes to speed up slow queries that hold locks for longer than necessary. A query that scans an entire table will hold locks for far longer than a query that uses a non-clustered index to find the exact rows it needs. Even one missing index can cause dozens of deadlocks a day on a busy instance.
Deadlocks don’t have to be a mysterious problem that takes days to troubleshoot. Once you know how to check the deadlock in SQL Server using the built-in System Health Session and interpret the deadlock graph, you can resolve most conflicts in 15 minutes or less. Don’t stop at just detecting deadlocks though – take the time to fix the root cause, whether that’s adjusting query order, shortening transaction length, or adding missing indexes, to keep your instance running smoothly for all users.