Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
Register Now.

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

Create a SQL Server Extended Events Session for DPA

Table of contents
Created by Anthony.Rinaldi_ret, last modified by Melanie Boyd on Sep 05, 2017

Views: 142 Votes: 0 Revisions: 3

Updated July 5, 2016

Overview

DPA uses the SQL Server 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 10.2 and later

Steps

  1. Create your own Extended Events Session on the monitored database. For information on creating the session, see this MSDN article.

    DPA uses the following CREATE statement by default:

    CREATE EVENT SESSION [your_session_name] ON SERVER
    ADD EVENT sqlserver.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)
    
    • DPA can query deadlock information from Event File and Ring Buffer targets. If an Extended Events Session is associated with multiple targets, DPA prefers the Event File target over the Ring Buffer target.
    • If you use a ring buffer target, SolarWinds recommends setting its 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.
    • When using an Event File target, SolarWinds recommends keeping the maximum total size of files (max_file_size and max_rollover_files) under 30 MB. Large event files may cause a large amount of I/O operations on the monitored database server, especially when starting the monitor after long periods of not monitoring.
  2. On the monitored database, start the session with the following command:
    ALTER EVENT SESSION [your_session_name] ON SERVER 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
16:13, 5 Sep 2017

Tags

Classifications

Public