If you’ve ever spent hours digging through messy SQL Server logs trying to figure out why your database is timing out and transactions are stuck, you know how frustrating unaddressed deadlocks can be. Most new database admins waste time manually scanning activity monitors or using expensive third-party tools, but you can cut that work down to minutes when you know how to check deadlock in sql server using query. I’ve supported SQL Server environments for small startups and enterprise e-commerce platforms over the past 9 years, and these query-based methods are the most reliable, low-effort way to diagnose deadlocks without adding extra overhead to your live instance.
How to Check Deadlock in SQL Server Using Query: 3 Core Scripts You Can Run Today
Before you run any of these scripts, there are a few quick checks you need to complete first to make sure you get accurate results without disrupting your workload:
- You have VIEW SERVER STATE permission on the SQL Server instance, as deadlock data is restricted to admin-level roles by default to protect sensitive transaction details
- Your default system_health extended events session is not disabled, as it stores 4-7 days of deadlock history for most small to medium workloads
- You test scripts on a staging instance first if you’re unfamiliar with them, to avoid accidentally adding extra load to your production server during peak hours
The first and most widely used script is the system_health extended events query, which pulls historical deadlock data directly from the default extended events file. It’s perfect for deadlocks that happened in the past few days, and you don’t need any extra setup to use it. The only downside is that older data gets overwritten as the file size hits its limit, so it won’t help you diagnose deadlocks that happened more than a week ago for most instances.
Second, the sys.dmtrandeadlocks DMV query lets you see active, ongoing deadlocks in real time. It’s ideal if you get a report of current timeouts and want to see exactly what’s happening right now. The catch here is that it only shows deadlocks that are currently active, so if SQL Server already rolled back a victim process to resolve the deadlock, you won’t see it in this DMV’s results.
Third, the trace flag 1222 output query pulls very granular deadlock data that includes exact query text, lock types, and transaction priority levels for both processes involved in the deadlock. You do have to enable trace flag 1222 first to use this, which adds a very small amount of overhead to your instance. I only recommend enabling this long-term if you have recurring deadlocks you can’t diagnose with the first two scripts.
What Data Your Deadlock Query Results Will Show You
It’s easy to get overwhelmed by the raw XML or table data that comes back from your deadlock queries, but you only need to focus on a few key pieces of information to diagnose the issue. First, you’ll see the deadlock victim process, which is the transaction SQL Server chose to roll back to break the deadlock. SQL Server usually picks the transaction with the lower rollback cost as the victim, but you can adjust transaction priorities if you need certain processes to never be chosen as the victim.
Next, you’ll see the exact resources that both processes were fighting over, usually a table row, page, or entire table lock. You’ll also see the lock type each process was holding and requesting, for example one process might hold an exclusive update lock on an order row while requesting a shared lock on a customer row, while the other process holds the shared lock on the customer row and requests the exclusive lock on the order row.
You’ll also get the full text of both queries involved in the deadlock, and the user or application that ran each query. That’s the most valuable data for fixing the root cause, because you can see exactly which parts of your application are causing the conflicting lock requests. I’ve had cases where two completely separate teams’ scheduled jobs were causing deadlocks at 2 AM every night, and we only found that out by looking at the application name in the deadlock query results.
Common Mistakes to Avoid When Running Deadlock Check Queries
One of the biggest mistakes I see new admins make is running heavy deadlock extraction queries during peak business hours, which adds extra CPU and I/O load to an already stressed server. If you’re dealing with a current outage, stick to the sys.dmtrandeadlocks DMV first, as it’s very lightweight and won’t add noticeable load. Pull historical deadlock data after peak hours when the server has extra resources to spare.
Another common mistake is assuming every single deadlock is a critical issue that needs immediate fixing. It’s normal for small, busy SQL Server instances to have 1-2 random deadlocks per week, especially during peak traffic or scheduled job runs. You only need to intervene if you see deadlocks happening more than 2-3 times per hour, or if they’re impacting critical user flows like checkout or login.
Many admins also forget to check deadlock history when troubleshooting recurring performance issues. They only check for active deadlocks when users report a problem, and miss the pattern of deadlocks happening at the same time every day when scheduled jobs run. Always pull at least 3 days of deadlock history when you’re investigating slow performance, even if you don’t see active deadlocks when you first start troubleshooting.
How to Use Deadlock Query Data to Fix Recurring Issues
Once you have your deadlock query results, the first thing to check is if both transactions are accessing tables in different orders. The vast majority of recurring deadlocks happen because one query updates Table A then Table B, while another query updates Table B then Table A. Adjusting both queries to access tables in the same order will fix 70% of recurring deadlock issues with very little work.
If the order is already consistent, the next step is to check if you can lower the transaction isolation level for the involved queries. Most workloads run perfectly fine with read committed snapshot isolation instead of the default read committed or repeatable read levels, which eliminates most shared lock requests entirely. Just make sure you test this change on staging first, as it changes how your queries see in-flight transaction data.
If those two fixes don’t work, check if the queries involved are running slowly because of missing indexes. Slow queries hold locks for longer periods of time, which drastically increases the chance of deadlock conflicts. I worked with an e-commerce client last year that had 12+ deadlocks a day on their order and customer tables during peak sales. All the deadlocks were fixed by adding two small non-clustered indexes that cut the runtime of their checkout update queries from 400ms to 20ms, so they released locks much faster.
If you still have deadlocks after those three steps, you can adjust transaction priorities for critical processes so they’re never chosen as the deadlock victim. This is a last resort, though, because it just shifts the impact to other processes instead of fixing the root cause of the lock conflict.
Deadlocks don’t have to be a mysterious, time-consuming problem to solve for your SQL Server instance. Once you know how to check deadlock in SQL Server using query, you can cut down your troubleshooting time from hours to minutes, and find the root cause without expensive tools or hours of log digging. Start with the three core scripts we covered, validate your results to avoid false positives, and focus on fixing the root cause instead of just killing stuck transactions. Over time, you’ll be able to prevent most deadlocks before they ever impact your end users.