Create a SQL Server Extended Events Session for DPA

11/21/2018 8:57 PM

5/10/2019 5:01 PM

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.

Database Performance Analyzer

1. Create your own Extended Events Session on the monitored database. For information on creating the session, see this MSDN article (© 2017 Microsoft, available at http://www.microsoft.com, obtained on November 21, 2018).

   
   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 11.0 and earlier prefers the Event File target over the Ring Buffer target.
     • DPA 11.1 and later prefers the target specified by the DEADLOCK_POLL_SQL_SERVER_PREFERRED_TARGET option.
   • 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 (© 2017 Microsoft, available at http://www.microsoft.com, obtained on November 21, 2018). 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.  (DPA 11.1 and later) Configure DPA to use proffered target:
   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 preferred target for deadlock collection, edit the DEADLOCK_POLL_SQL_SERVER_PREFERRED_TARGET system option.
   4. If you want to configure one SQL Server database instance to use the preferred target for deadlock collection, click the DB Instance Options tab. Select the SQL Server database instance, and edit the DEADLOCK_POLL_SQL_SERVER_PREFERRED_TARGET database instance option.
4. 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.
5. 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.

Disclaimer: Please note, any content posted here is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.