querying dpa_deadlock_capture xe

rottengeek​,

Really good questions.  I haven't heard of that issue before. Are you saying you unregister the instance in DPA and don't see system_health track deadlocks after that is done?

sorry it took so long to reply - but, yes. Instances which are not being monitored have the trace, and the system_health trace is not capturing the deadlock graph - which means it is kind of a pain to get good deadlock information.

rottengeek​

So when you put up this discussion I sent the questions and checked some with the DPA engineering team on this issue. The reply from them was "We are not touching the system_health session and I don't know about any behavior on SQL Server that should stop collecting the deadlocks when another session is created.

I verified and both the default system_health and dpa_deadlock_capture is collecting the deadlocks in ring buffer."

So if you are not seeing deadlock data on the default system_health trace it shouldn't be related to DPA. One issue might be what we internal call the Hydration problem.

4MB of BINARY data != 4MB of XML data => not all deadlocks appear in XML

Ring buffer works as FIFO (first in first out), so potentially, if ring_buffer will be full all the time, we will never have opportunity to see the first deadlocks (EES can collect also other events than deadlocks, and those events takes its place in ring buffer as well).

Default configuration

Default configuration depends on type of monitored SQL Server instance:

• Self-Managed instance: if DEADLOCK_POLL_ENABLED is set to true and DEADLOCK_POLL_SQL_SERVER_SESSION_NAME  is holding default value ('dpa_deadlock_capture'), DPA will automatically create EES called 'dpa_deadlock_capture' (or use existing one with that name but than DPA cannot ensure proper configuration) with following settings:
  • target type = ring_buffer
  • max_memory = 2048 KB.
• RDS instance: because there is no way how to create new EES (no user can have privileges to do that), 'system_health' session is used in case of RDS (DEADLOCK_POLL_SQL_SERVER_SESSION_NAME will be automatically configured to contain 'system_health' value). 'system_health' session will be used will following settings:
  • target_type = event_file - 'system_health' session has configured both targets, but as ring_buffer is not reliable, we prefer to use event_file.

I don't know from what you list here if this is the case. You can always log a support case and we can investigate more.