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:
- Capture Deadlocks (SQL 2005+)
- Capture Autogrow
- Capture Blocking (SQL 2005+)
Non-query activities data collection
You can select to collect non-query activity 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.
To capture non-query activities for monitored pre-SQL Server 2008 instances, enable the SQL Trace collection option. Note that enabling this option can degrade performance on your SQL Server.
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 5 (seconds) but can be modified as desired.
Since collecting information about blocked processes in an SQL server is resource-intensive, IDERA recommends setting 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.
Modifying the Activity Monitor's blocked process threshold automatically changes the blocked process threshold value in your monitored SQL Server instance.
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.