DeadlockCollector is a free T-SQL solution, not a PowerShell module or an application: two scripts set up a dedicated database and an hourly SQL Agent job that reads deadlock graphs straight out of the system_health Extended Events session every SQL Server already runs. No custom trace, no separate service, just a persisted, deduplicated history you can query in SSMS.
Every deadlock already lands in system_health's ring buffer, but it ages out, nobody's watching, and there's no history to spot a pattern in.
| Without it | With DeadlockCollector |
|---|---|
| Deadlocks age out of the ring buffer before anyone looks | Collected hourly into a dedicated, persistent database |
| Capturing deadlocks means configuring a custom XE session | Reads the system_health session every instance already runs |
| No record of which hour or which database gets hit hardest | Views aggregating by day, by hour, and by database |
| Re-running a collector re-imports the same deadlocks | Deduplicated by hash, safe to run repeatedly |
| An AG secondary generates false alarms from a read-only copy | Skips collection entirely on a read-only replica |
| SQL Server | 2008 R2 or later, anywhere the system_health session exists; explicitly tested on 2016, 2019, and 2022 |
| SQL Server Agent | Running, to execute the hourly collection job |
| Rights to install | sysadmin, the setup script creates a database, objects, and a server-level Agent job |
| Optional | SQL Server 2012 or later, only if importing a saved .xel file instead of reading the live ring buffer |
| Dependencies | None, pure T-SQL. No PowerShell, no dbatools, no installer |
system_health Extended Events session, the one every SQL Server instance runs by default, not something you have to set up.dbo.Collect once an hour.dbo.Collect checks whether the database is read/write and skips the run entirely if it's a read-only Availability Group secondary.dbo.InsertDeadLock extracts the victim list, process list, and resource list from the deadlock XML, plus per-process detail, database, SPID, login, host, application name, wait resource, lock mode, isolation level, and input buffer, for both processes involved.sqlhandle is used to pull the actual SQL text and query plan from the plan cache, if it hasn't been evicted yet.dbo.InsertDeadLock also accepts an @XESource parameter pointing at a saved .xel file instead of the live ring buffer, for importing a capture from elsewhere, that path requires SQL Server 2012 or later.
| Object | Purpose |
|---|---|
| dbo.DeadLock | The central table, one row per deadlock, with per-process detail and the full graph as XML |
| dbo.Collect | Entry point called by the SQL Agent job, checks read/write status, then calls InsertDeadLock |
| dbo.InsertDeadLock | Parses the deadlock XML and writes new, deduplicated rows to dbo.DeadLock |
| dbo.deleteDeadLock | Retention cleanup, removes rows older than a given date |
| dbo.sel_xml_List | Scalar function extracting the victim/process/resource lists from a deadlock XML graph |
| dbo.vwvDeadlockInfos | Base view over every captured deadlock |
| dbo.vwvDeadLockByDay | Deadlock and victim counts per day |
| dbo.vwvDeadLockByDayHour | Deadlocks per day and hour, for spotting a recurring time-of-day hotspot |
| dbo.vwvDeadLockByDayAndDatabase | Deadlocks per day and database |
| dbo.vwvDeadLockCombinationBySQLText | The most frequent SQL-text combinations, for spotting a repeat offender query pattern |
There's no custom GUI. Query dbo.vwvDeadlockInfos or the underlying table, and click the graph XML value in the SSMS results grid, SSMS renders the native deadlock diagram, exactly as if you'd captured it by hand from an XE session.
-- Step 1: create the database and objects :r SetUpDeadlockCollectorDb.sql -- Step 2: create the hourly SQL Agent job :r SetUpDeadlockCollectorJob.sql
Run both in SSMS or via SQLCMD as a sysadmin. The first script creates the database, tables, views, and stored procedures; the second creates the SQL Agent job that calls dbo.Collect hourly. dbo.deleteDeadLock is there for retention, run it on a schedule of your own choosing to trim rows past a given date.
Get-sqmDeadlockReport?sqmSQLTool has its own deadlock command, and the two solve related but different problems, use one, the other, or both.
| DeadlockCollector | Get-sqmDeadlockReport | |
|---|---|---|
| Type | T-SQL, installed once | PowerShell cmdlet (sqmSQLTool, requires dbatools) |
| Trigger | Scheduled hourly SQL Agent job | On-demand, run whenever you need a report |
| Storage | Dedicated database, deduplicated | None, reads the ring buffer live each time |
| Time window | Continuous, since the day it was installed | Configurable, defaults to the last 24 hours |
| AG-awareness | Skips read-only secondaries automatically | Not AG-aware |
| Output | SQL views, SSMS-native deadlock diagrams | PowerShell objects, or .xdl files for SSMS |
DeadlockCollector gives you a persistent baseline you never have to remember to run; Get-sqmDeadlockReport is the tool for a quick on-demand pull in the middle of an incident.
DeadlockCollector is released under the MIT License, no trial, no activation, same as the rest of the free tools in this suite.
Get-sqmDeadlockReport
An on-demand PowerShell cmdlet for pulling a deadlock report during an incident, without installing anything first.
All 132 commands across AlwaysOn, backup, security, and performance, grouped by category.
Free, MIT-licensed, clone the repository and run the two setup scripts against your own instance.
Every release, what changed, and why.