Set activity monitor options

The Activity Monitor window allows you to enable or disable monitoring of non-query activities. If you enable the Activity Monitor, you can select the settings that are used.

The types of non-query events to capture include:

• Deadlocks (SQL 2005+)
• Autogrow
• Blocking (SQL 2005+)

Non-query activities data collection

You can select to collect non-query activities data using Extended Events (Use Extended Events radio button) or SQL Trace (Use SQL Trace radio button).  For instances running SQL Server 2012 or higher, the Activity Monitor uses by default Extended Events.  First introduced in SQL Server 2008, Extended Events provide a new mechanism to capture information about events inside the Database Engine and diagnose performance problems. This functionality is highly efficient and lightweight.  For more information about using Extended Events, see the Microsoft document, Extended Events.

Capturing deadlocks

Capturing deadlock information allows the associated deadlock alert to provide details on deadlocked processes on the Alerts view. Deadlock monitoring is supported on monitored instances running SQL Server 2005 or greater and is dependent on the Activity Monitor. To access this information, check Enable the Activity Monitor and Capture deadlocks (SQL 2005+) in the Activity Monitor window. Details provided include:

• SPID
• Hostname
• Login Name
• Client Application
• Database
• Status
• Wait Time
• Wait Resource
• Transaction Count
• Input Buffer
• Lock Object
• Lock Mode
• Lock Owners
• Lock Waiters

Capturing autogrow events

Capturing autogrow information allows the associated autogrow alert to provide information about log and data growth on the Alerts view. To access this information, check Enable the Activity Monitor and Capture Autogrow in the Activity Monitor window.

Capturing blocking events

Capturing blocking information allows the associated blocked alert to provide details on blocking and blocked sessions on the Alerts view. Blocking monitoring is supported on monitored instances running SQL Server 2005 or greater and is dependent on Activity Monitor. To access this information, check Enable the Activity Monitor and Capture Blocking (SQL 2005+) in the Activity Monitor window. Key details provided for blocking and blocked sessions include:

Blocking process details:

• Session ID
• Hostname
• User Name
• Application
• Database
• Last Batch Started
• Transaction ID
• Open Transactions

Blocked process details:

• Session ID
• Hostname
• User Name
• Application
• Database
• Last Batch Started
• Transaction ID
• Open Transactions
• Wait Resource

Blocked process threshold

SQL Server 2005+ includes a new SQL Server Profiler event, the Blocked Process report. This event helps identify blocking issues and provides relevant information to solve these issues. In SQL Diagnostic Manager, you can take advantage of this feature by setting the blocked process threshold value according to your needs. To access the Blocked Process Threshold option, enable Capture Blocking (SQL 2005+) in the Activity Monitor window. Note that when this option is enabled, the blocked process threshold value is automatically set to 30 (seconds) but can be modified as desired.

Since collecting information about blocked processes in SQL server is resource-intensive, IDERA recommends to set the blocked process threshold value to at least 5 (seconds) or your deadlock monitor runs constantly. For more information on this topic, see the Microsoft document Increase or Disable Blocked Process Threshold.

Access the Activity Monitor tab

You can access the Activity Monitor tab of the Monitored SQL Server Properties window by right-clicking the appropriate SQL Server instance, and then selecting Properties. Click Activity Monitor when SQL Diagnostic Manager displays the Monitored SQL Server Properties window.