How the SQL Server deadlocks collection gathers deadlock information (4307773)

The deadlocks collection uses five collections to obtain information about SQL Server deadlocks.

1. DBSS_Deadlock – Creates and starts trace events for deadlocks.
2. DBSS_Error_Log_Data – Collects and show errors from the error log, amongst these are deadlocks.
3. DBSS_Is_Trace_Exists – Check if all the deadlock relevant trace events are on.
4. DBSS_Latches_Locks – Returns a record set of all Latches Locks related statistics metrics it’s done from the OS performance counters (amongst them - deadlocks).
5. DBSS_Lock_Statistics – Works the same way as DBSS_Latches_Locks with exclusion of latches.

We combine the usage of DMVs and trace events for the deadlocks.

This information is then displayed in the SQL Server deadlocks dashboard and is used in Foglight reports.

There are three alarms:

1. DBSS – Deadlocks, based on the “DBSS_Latches_Locks” collection.
   The alarm pops in warning whenever the average deadlocks per seconds exceed 0.01.
2. DBSS - Deadlock Details, based on a collection “Deadlock - Summary - Instance Level”.
   This alarm is disabled by default because the rule would raise alarm for any deadlock data which is collected by the agent. Meaning, that if the agent collect 4 deadlocks at 19:49, then 4 alarms would be raised. This is why the rule is not enabled by default, since the user could end up with many alarms. 
3. DBSS - Deadlocks Baseline Deviation – This is a baseline alarm on the DBSS_Lock_Statistics (Same DMV as the “DBSS – Deadlocks” alarm).
   The alarm is raised when number of deadlocks crosses high level baseline.

Conclusion
The DMV sys.dm_os_performance_counters is used to detect new deadlocks and to find deviations.
The trace events are used on screen to display deadlock incidents; that is done directly by the agent.

This method of collecting deadlock information is compatible with SQL Server 2005 and has been replaced by using extended events as part of enhancement SSFOG-424 in the 5.9.4.20 version of the SQL Server cartridge.

SQL PI collects deadlocks from the sys.dm_tran_locks and does not use SQL Traces or the collections listed above.