Submit a ticketCall us

Get a crash course on Network Monitoring delivered right to your inbox
This free 7-day email course provides a primer to the philosophy, theory, and fundamental concepts involved in IT monitoring. Lessons will explain not only how to perform various monitoring tasks, but why and when you should use them. Sign up now.

Home > Success Center > Database Performance Analyzer (DPA) > Create an Azure SQL Extended Events Session for DPA

Create an Azure SQL Extended Events Session for DPA

Table of contents

Updated February 17, 2017

Overview

DPA uses the Azure SQL Extended Events Session to collect information about deadlocks that occurred in the database. By default, DPA creates its own Extended Events Session named dpa_deadlock_capture.

If you want to provide your own Extended Events Session to collect deadlock information, follow the instructions in this article.

Environment

  • DPA 11.0 and later

Steps

  1. Create your own Extended Events Session on the monitored database. For information on creating the session, see this MSDN article and this Microsoft article for specifics on Azure databases.

    DPA uses the following CREATE statement by default:

    CREATE EVENT SESSION [your_session_name] ON DATABASE
    ADD EVENT sqlserver.database_xml_deadlock_report
    ADD TARGET package0.ring_buffer(SET max_events_limit=(1000), max_memory=(2048))
    WITH (MAX_MEMORY = 2048KB,
          EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
          MAX_DISPATCH_LATENCY = 30 SECONDS,
          MAX_EVENT_SIZE = 0KB,
          MEMORY_PARTITION_MODE = NONE,
          TRACK_CAUSALITY = OFF,
          STARTUP_STATE = ON)
    
    • Only the Ring Buffer target is supported for deadlock collection.
    • SolarWinds recommends setting the Ring Buffer target's MAX_MEMORY to 2048 kB or less. Otherwise, DPA may collect deadlock information with non-deterministic delay due to truncation of the data provided by SQL Server. See this MSDN article for more information. The issue may also affect the accuracy of deadlock summary data.
  2. On the monitored database, start the session with the following command:
    ALTER EVENT SESSION [your_session_name] ON DATABASE STATE = START
  3. Configure DPA to use the custom session.
    1. In DPA, click Options > Administration > Advanced Options.
    2. Select the Support Options check box.
    3. If you want to configure all your current and future SQL Server database instances monitored by DPA to use the custom session for deadlock collection, edit the DEADLOCK_POLL_SQL_SERVER_SESSION_NAME system option.
    4. If you want to configure one SQL Server database instance to use the custom session for deadlock collection, click the DB Instance Options tab. Select the SQL Server database instance, and edit the DEADLOCK_POLL_SQL_SERVER_SESSION_NAME database instance option.
  4. Restart the monitors of the database instances for which you changed the deadlock poll session name.
    1. Click Home, and locate the database instance in the list.
    2. Click Action > Stop Monitor.
    3. Click Action > Start Monitor.
Last modified
10:56, 17 Feb 2017

Tags

Classifications

Public