Applications Systems

Adjust the max memory attribute of an Extended Events Session created by DPA

How to update the max memory attribute for SQL Server database instances with deadlock data collection enabled.

First published date

10/29/2018 4:06 PM

Last published date

10/29/2018 4:06 PM

Overview

When you enable deadlock data collection and DPA begins monitoring a SQL Server database instance, it creates an Extended Events Session with a Ring Buffer target. The session is named dpa_deadlock_capture. When DPA creates the session, the value of the DEADLOCK_POLL_SQL_SERVER_DEFAULT_SESSION_MAX_MEMORY_KB option in DPA is set as the max memory attribute of the created session and of its target.

If you adjust the DEADLOCK_POLL_SQL_SERVER_DEFAULT_SESSION_MAX_MEMORY_KB option after the Extended Events Session dpa_deadlock_capture was created, you must perform the steps below in order for the updated max memory option to take effect.

Product section

Database Performance Analyzer

Resolution

  1. On the DPA home page, locate the database instance in the list.
  2. Click Action > Stop Monitor.
  3. On the monitored SQL Server, execute following SQL query:

    DROP EVENT SESSION [dpa_deadlock_capture] ON SERVER

  4. On the DPA home page, locate the database instance in the list.
  5. Click Action > Start Monitor.

Important notes 

You must enable deadlock data collection on the monitored database instance in order for DPA to create the dpa_deadlock_capture session. DPA will create the session only if the DEADLOCK_POLL_SQL_SERVER_SESSION_NAME option is set to its default value of dpa_deadlock_capture.

Enable deadlock data collection 

  1. On the DPA home page, click the database instance in the list.
  2. Below the chart, click the Deadlocks tab.
  3. Click Enable Deadlock Collection.
  4. Decide whether to enable deadlock collection for this instance or all instances.