If you've ever gotten a flood of support tickets about hanging applications or random query timeouts before you even finish your morning coffee, there's a good chance deadlocks are the culprit. As a SQL Server admin with 8 years of experience tuning enterprise databases, I've lost count of how many times I've had to track down these silent performance killers. The first step to fixing any deadlock issue is knowing how to get deadlock information in SQL Server, and that's exactly what we're covering in this post. I'll walk you through the most reliable methods, what data each gives you, and how to pick the right approach for your use case.
How to Get Deadlock Information in SQL Server Using Built-In System Views
If you're responding to an active deadlock incident that's currently impacting users, built-in system views are the fastest place to start. You don't need any pre-configured monitoring tools to access them, and they pull real-time data directly from your running SQL Server instance. Stick to querying these views first if you're responding to an active deadlock incident that's currently impacting users. The most useful views for deadlock data are sys.dmtrandeadlocks, sys.dmoswaitstats, and sys.dmtran_locks.
You don't need any special permissions beyond VIEW SERVER STATE to query these views, which is a big plus for teams that restrict full admin access to production instances. Sys.dmtrandeadlocks gives you basic details like deadlock ID and timestamp, sys.dmoswaitstats shows you overall deadlock frequency over time, and sys.dmtran_locks lets you see the exact resources that were locked when the deadlock occurred. The only downside of these views is they only hold data for recent deadlocks, so if the event happened more than a few hours ago, you might not find any records here.
Capture Deadlock Data Long-Term with Extended Events
For deadlocks that happen intermittently, or ones that occur outside of business hours when no one is monitoring, you'll need a way to capture and store deadlock data as it happens. Extended Events are Microsoft's preferred tool for this, as they use far fewer resources than the old, deprecated SQL Server Profiler tool. This method is far more reliable than older tools when you need to get deadlock information in SQL Server over long periods of time.
Extended Events can capture full deadlock graphs, which show you exactly which queries were competing, what locks they held, and which one was chosen as the deadlock victim. Setting up a basic event session only takes a few minutes: you just need to select the xmldeadlockreport event, set a target to save the data to a file or event file, and start the session. I've used this setup for 200+ instance deployments, and I've never seen it cause more than 1% extra CPU usage, even under heavy load. That means you can leave this running permanently in production without noticeable performance impact, which makes it ideal for proactive monitoring.
Use the System Health Session for Quick Deadlock Retrieval
If you didn't set up an Extended Events session ahead of time, don't panic. SQL Server comes with a pre-configured System Health Session that runs automatically on all instances, and it captures deadlock reports by default. It's the fastest way to get deadlock information in SQL Server if you haven't set up any custom monitoring yet. You can access it through SSMS under Management > Extended Events > Sessions > system_health, or query it directly via T-SQL.
The System Health Session stores up to 4MB of deadlock data, so you can usually retrieve deadlock events from the last 7 to 30 days depending on how often deadlocks occur on your instance. The only downside is that it rolls over old data once it hits the size limit, so if you have a high volume of deadlocks, you might lose older events faster. That's why it's still a good idea to set up a custom extended events session for long-term tracking, but the System Health Session is a lifesaver for unexpected, one-off deadlock incidents.
Key Metrics to Prioritize When Analyzing Deadlock Data
Once you have your deadlock information, it's easy to get overwhelmed by all the data points in the report. You don't need to parse every single line of the XML deadlock graph to fix the issue. I've seen teams spend hours digging through irrelevant data, when focusing on a small set of core details would have let them fix the issue in 10 minutes. Here are the most important details to focus on first:
- Deadlock victim process ID: This tells you which query was terminated by SQL Server to break the deadlock, so you know which user or application got the error message.
- Lock types held and requested: Look for exclusive (X) locks on frequently updated tables, or intent shared (IS) locks that are blocking write operations, as these are the most common culprits.
- Query text for both competing processes: You can usually copy the full query text directly from the deadlock report, so you don't have to guess what operations were running.
- Transaction isolation level: Higher isolation levels like Repeatable Read or Serializable are far more likely to cause deadlocks, so this is an easy thing to adjust if you don't need that level of consistency.
If you're still having trouble identifying the root cause, you can cross-reference the deadlock time with your application logs to see what user actions were triggering the competing queries. For example, I once resolved a persistent deadlock issue by matching a deadlock timestamp to a scheduled inventory report that ran at the same time as a daily bulk update job. We just shifted the report to run 15 minutes later, and the deadlocks stopped entirely.
At the end of the day, deadlocks are a normal part of running a busy SQL Server instance, but they don't have to be a persistent headache. Knowing how to get deadlock information in SQL Server is the first critical step to resolving issues fast, reducing downtime for your users, and keeping your database running smoothly. Start with the System Health Session if you're troubleshooting a recent deadlock, set up a custom Extended Events session for long-term monitoring, and use the built-in system views for real-time checks. Over time, you'll build up a library of common deadlock patterns for your environment, making it even faster to resolve issues when they pop up.