SQL Server deadlocks not displayed in dashboard on 5.9.4.20 or higher cartridge (4230687)

After an upgrade to a 5.9.4.20 or higher version of the SQL Server cartridge, deadlocks are not detected in the SQL Server Deadlocks Dashboard. The same deadlocks can be viewed in SQL Server Management Studio.

Beginning with the SQL Server cartridge version 5.9.4.20 and higher, we began to use extended events default means to capture deadlock data. Deadlock events are obtained by reading the data collected by the default system_health extended event. Deadlocks will not be captured if user has disabled the system_health session on their instance.

You can check that is it enabled using SQL Server Management Studio

WORKAROUND

Switch the database agent agent status properties (ASP) to use the deadlock collection instead of "Extended Events"

1. Navigate to Administration | Agents | Agent Status
2. Select a DB_SQL_Server agent
3. Click Edit Properties
4. Click the link to Modify the private properties for this agent
5. Scroll down to Deadlock Parameters section
6. Change Deadlock working mode from Auto to Trace
7. Save the changes
8. Return to the Agent Status page
9. Deactivate the SQL Server agent
10. Activate the SQL Server agent
11. Wait a couple of minutes, then check the SQL Server deadlocks dashboard