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.