If you’ve ever gotten a "Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim" error in your SQL Server environment, you know how frustrating it can be for end users and DBAs alike. Most legacy deadlock tracking methods like trace flags or Profiler add unnecessary overhead to production servers, which is why more teams are switching to lightweight, built-in tools. If you want a low-impact, reliable way to capture deadlock events, learning how to find deadlock in SQL Server using extended events is one of the most useful skills you can add to your database admin toolkit.
How to Find Deadlock in SQL Server Using Extended Events: Initial Setup
Extended events are available in all supported versions of SQL Server, and they use far fewer resources than older tracking methods, so they’re safe to run on even high-traffic production environments. Before you start, you’ll need two key permissions: ALTER ANY EVENT SESSION to create and modify event sessions, and VIEW SERVER STATE to access captured event data. You can set up your deadlock tracking session either through the SSMS GUI or with T-SQL, depending on your preference.
If you use the GUI, navigate to Management > Extended Events > Sessions in SSMS, right click to create a new session, and select the built-in deadlock capture template to skip manual configuration. If you prefer T-SQL, you can run a basic script to create a session that captures only the xmldeadlockreport event, saves data to 10 rollover files of 100MB each, and starts automatically after server restarts. Never run untested event sessions on production servers first – test your setup in staging first to confirm you’re not capturing unnecessary events that add unexpected overhead.
Once your session is set up, start it and leave it running. It will capture every deadlock event as it occurs, with no manual intervention required. One of the biggest benefits of learning how to find deadlock in SQL Server using extended events is that you don’t have to actively run a trace or keep a tool open to capture deadlocks when they happen.
Key Deadlock Data Points You’ll Capture With Extended Events
When a deadlock occurs, the xmldeadlockreport event captures a complete, structured record of every detail related to the incident. You don’t have to parse messy, unstructured trace logs to find the information you need to resolve the issue. The data you’ll get includes:
- Deadlock victim process ID and the exact query that was terminated to break the deadlock
- Winning process details including the query that held the conflicting lock and completed successfully
- Lock types involved (row lock, page lock, table lock) and the specific database objects being fought over
- Timestamps and transaction isolation levels for both processes to help identify root cause
The xml deadlock report captures every detail you need to resolve the issue, no guesswork required – unlike older methods that often only gave partial data, forcing you to piece together what happened from scattered logs. You can view the deadlock as a raw XML file, or click the report in SSMS to open a visual deadlock graph that makes it easy to see the relationship between the two conflicting processes at a glance.
From my own experience troubleshooting deadlocks for mid-sized e-commerce platforms, 90% of recurring deadlocks come down to one of two root causes: missing nonclustered indexes that force queries to hold full table locks for long periods, or queries that access tables in reverse order across two concurrent transactions. The data you get from extended events makes it trivial to spot both of these issues in seconds.
How to Analyze Captured Deadlock Events Without Extra Tools
Once you have captured deadlock events, accessing and analyzing them takes just a few clicks in SSMS. Right click on your deadlock capture session, select "View Target Data", and you’ll see a list of all captured events. Filter for the xmldeadlockreport event type to see only deadlock records, and sort by timestamp to find events related to specific user reports of errors.
Click on the XML field for any deadlock event to open the visual deadlock graph. The circle marked with an X is the deadlock victim, the other circle is the winning process, and the lines between them show which locks each process held and which they requested. Always check the order of table access first when reviewing deadlock graphs – that’s the most common fix for recurring deadlocks, and it’s usually easy to adjust query order to eliminate conflicts.
If you’re troubleshooting a high volume of deadlocks, you can export the captured event data to a temporary table and aggregate by query hash to find the most frequent problematic queries. You don’t need expensive third-party monitoring tools to do this analysis – all the functionality is built directly into SSMS. Avoid filtering out low-frequency deadlocks entirely – even one deadlock a week can point to a gap in your indexing or query design that will get worse as your user base grows and transaction volume increases.
You can also save deadlock graphs as XDL files to share with your development team, so they can see exactly what queries are causing issues without needing direct access to your production SQL Server environment. This makes it much easier to collaborate on fixes between DBAs and engineering teams.
Common Pitfalls to Avoid When Using Extended Events for Deadlock Tracking
While extended events are far more user-friendly and low-impact than older deadlock tracking methods, there are a few common mistakes that can make them less effective, or even cause unexpected issues on your server. The first mistake is adding too many extra events to your deadlock capture session. A lot of new DBAs try to combine deadlock tracking with general query performance monitoring in the same session, which increases resource usage and makes it harder to find deadlock data quickly.
Keep your deadlock tracking event session separate from other performance monitoring sessions to minimize resource usage and keep your deadlock data organized. Another common mistake is leaving the event session running with no limits on file size. If you don’t set a max file size and rollover limit, your event files can fill up your server’s disk drive over time, leading to unexpected outages. For most production environments, 10 rollover files of 100MB each is a safe starting point, and it will hold months of deadlock data for even fairly busy servers.
Don’t ignore system deadlocks either. Some deadlocks are between internal SQL Server system processes, not user queries, but if you see them appearing frequently, it can be a sign of underlying issues like tempdb contention or misconfigured system settings. Schedule a weekly 10-minute review of deadlock events to catch small issues before they turn into widespread outages that impact your end users.
It’s also important to avoid the temptation to just raise the deadlock priority for critical processes instead of fixing the root cause of deadlocks. This just pushes the error to less critical workflows instead of solving the problem, and it will lead to more issues down the line as your transaction volume grows.
Deadlocks are an unavoidable part of running a busy SQL Server environment, but they don’t have to be a constant source of headaches for your team. Learning how to find deadlock in SQL Server using extended events gives you a low-overhead, reliable way to capture every detail you need to resolve issues fast, without putting extra strain on your production servers. You don’t need expensive third-party monitoring tools to get full visibility into deadlock events – the built-in extended events feature has everything you need to identify, analyze, and fix even the most tricky recurring deadlock issues. If you haven’t switched from trace flags or Profiler for deadlock tracking yet, setting up a test extended events session in your staging environment this week is a great first step.