3 Replies Latest reply on Jun 5, 2019 5:42 PM by jaminsql

    querying dpa_deadlock_capture xe

    rottengeek

      when we un-monitor a server, (hoping for more licenses!!) the dpa_deadlock_capture is left, and deadlocks are not picked up in the system health xe.

      I'm wondering if anyone has written a handy query to read the dpa_deadlock_capture, as it has a ring buffer target, and also - how do i get things to start going back to system health?

       

      I can of course parse out the xml myself but i thought if someone else has already done it perhaps they could share.

       

      Thanks!

        • Re: querying dpa_deadlock_capture xe
          jaminsql

          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?

            • Re: querying dpa_deadlock_capture xe
              rottengeek

              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.

                • Re: querying dpa_deadlock_capture xe
                  jaminsql

                  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.