Applications Systems

DPA deadlock collection not enabled by default

When you are monitoring a SQL Server 2012 or later database instance, DPA has the ability to collect and monitor information about deadlocks. Deadlock collection is not enabled by default because it uses Extended Event Session (EES), which is known for bugs and glitches. You can decide when and where to enable deadlock collection. This article describes how to enable deadlock collection for one database instance or for all supported database instances.

First published date

11/29/2018 10:23 PM

Last published date

8/4/2020 3:34 PM

Overview

Note: This feature is currently available for SQL Server 2012 and later. If you are monitoring an earlier version of SQL Server or another type of database (Oracle, for example), DPA does not display the Deadlocks tab.

 

When you are monitoring a SQL Server 2012 or later database instance, DPA has the ability to collect and monitor information about deadlocks. Deadlock collection is not enabled by default because it uses Extended Event Session (EES). Historically, the EES was known for bugs and glitches, so we wanted to allow the user to decide when and where to enable deadlock collection. When deadlock collection is enabled, DPA will configure your SQL Server instance with a low level extended event session to capture occurrences of deadlocks.

Product section

Database Performance Analyzer

Resolution

You can enable deadlock collection in Advanced Options, or from the UI. It can be enabled for a specific database instance or for all supported database instances.

From the UI

  1. From the DPA home page, click a SQL Server database instance to view details.
  2. On the lower half of the screen, click the Deadlocks tab.
    If deadlock collection is not enabled, the following button is displayed.
  3. Click Enable Deadlock Collection.
    A dialog displays information about the deadlock collection feature.
  4. Click one of the following:
    • Enable for THIS monitor enables deadlock collection only for this SQL Server database instance.
    • Enable for ALL monitors  enables deadlock collection for all supported SQL Server database instances.

From Advanced Options 

  1. Access the Advanced Options page:
    • In DPA 12.1 and later, click Options in the main menu. Under Administration, click Advanced Options.
    • In DPA 12.0 and earlier, click Options in the main menu. Then click the Administration tab and click Advanced Options.
  2. If you want to enable it for a specific database instance, click the DB Instance Options tab and select the instance. To enable it for all supported SQL Server database instances, stay on the System Options tab.
  3. Select the Support Options box and find DEADLOCK_POLL_ENABLED.
  4. Change the value to True.