The Blocking view provides a blocking tree, along with the lead blockers, for the selected SQL Server instance. A lead blocker is a session that blocks at least one session, which can then block other sessions.
You can use the History Browser to view past performance and go back to the point in time to view the sessions involved with the blocking alerts you receive.
Navigate through the blocking tree to find sessions that cause blocks, and then either trace sessions to find out what is causing the blocks or stop the session to have it removed. To trace a blocked session or lead blocker select it from the list and click Trace Session. To stop a blocked session, or lead blocker, select it from the list, and click Kill Session.
Deadlocks have no lead blockers and are circular in nature. For this reason, SQL Diagnostic Manager does not display deadlocks in the Blocking Tree but does display instances of deadlocks in the Blocking Chart. In addition, SQL Diagnostic Manager does not count self-blocking sessions as blocked or blocking sessions.
Access the Blocking view
You can open the Blocking view of the SQL Diagnostic Manager Sessions tab by selecting the appropriate SQL Server instance, and then clicking Sessions > Blocking.
Trace Session
When you click Trace Session, the Session Trace window opens. The Session Trace provides session statistics and information including the following:
- Overview of the session, including the CPU Time, Row Count, Lock Wait Timeout, Physical Reads and Write metrics.
- Last Command executed.
- All the SQL Server statements.
Blocking chart
The Blocking chart displays the number of blocked sessions, lead blockers, and total deadlocks at a given point in time. The total deadlocks represent the total number of deadlocks that have occurred since the last time the pane was refreshed.
You can right-click the chart and either print, save as image, or export this data to Microsoft Excel. In addition, you can select Toolbar for advanced customization options, such as changing the chart color scheme and the type of chart shown.
Blocking Sessions Report
The Blocking Sessions Report (Block Reports) displays blocking and blocked sessions’ information. To access the Blocking Sessions Report in the Alert view, select the Show Block Details option in the right-click context menu. You can also access the same information on the Blocking View by selecting a specific SQL Server instance, pointing to Sessions, and then selecting the Blocking ribbon.
Key information provided for blocking and blocked sessions includes:
Blocking Process Details:
- Session ID
- Host Name
- User Name
- Application
- Database
- Last Batch Started
- Transaction ID
- Open Transactions
Blocked Process Details:
- Session ID
- Host Name
- User Name
- Application
- Database
- Last Batch Started
- Transaction ID
- Open Transactions
- Wait Time (ms)
- Wait Resource
This feature allows you to export statistics in XML format.
To retrieve the blocking sessions report for SQL Diagnostic Manager, select the "Non-Query activities>Capture Blocking (SQL 2005+)" check box in the Activity Monitor Tab.
Deadlock Sessions Report
The Deadlock Sessions Report (Deadlock Reports) displays deadlock sessions information. To access the Deadlock Sessions Report in the Alert view, select the Show Block Details option in the right-click context menu. You can also access the same information on the Blocking View by selecting a specific SQL Server instance, pointing to Sessions, and then selecting the Blocking ribbon.
Key information provided for deadlock sessions includes:
Deadlock Process Details:
- Session ID
- Status
- Host Name
- User Name
- Execution Context
- Application
- Database
- Last Batch Started
- Last Batch Completed
- Last Transaction Started
- Transaction ID
- Transaction Name
- Open Transactions
- Wait Time (ms)
- Wait Type
- Wait Resource
This feature allows you to export statistics in XDL format.
To retrieve the deadlock sessions report for SQL Diagnostic Manager, select the "Non-Query activities>Capture deadlocks (SQL 2005+)" check box in the Activity Monitor Tab.