The Blocking Session Wait Time (Seconds) alert provides the time in seconds that a SQL Server session blocks other sessions.
By default, locked sessions waiting for a lock wait forever, which is not optimum behavior from an end-user point of view. You can change this default behavior by adding SET LOCKTIMEOUT 60000
to the top of the first T-SQL batch after making a connection.
The Active Alerts view provides additional information on the Blocking Session Wait Time (Seconds) alert. To access this information, refer to the right-click context menu after having organized your alert view by metric. This menu grants access to real time alert information, historical view, block details, and online help specific to the alert.
To access similar information on the Blocking view, select a specific SQL Server instance, point to Sessions, and then select the Blocking ribbon. |
The Blocking Sessions Report 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.
Key information provided for blocking and blocked sessions includes:
Blocking Process Details:
Blocked Process Details:
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. |
If you experience more than one blocked session on an average day, your site may experience one of the following issues:
BEGIN TRANSACTION
statement with no corresponding COMMIT TRANSACTION
statement. You must correct the T-SQL.BEGIN TRANSACTION
statement but where the COMMIT TRANSACTION
statement is in a following T-SQL batch that is only executed once the end-user confirms the transaction. You must correct the T-SQL.Where your transactions run longer than they should and clash, consider drastically reducing the time that a lock is held by:
Create an alert response bundle with the Blocking Session Wait Time (Seconds) alert and related alerts. For additional information, see Create alert response bundles. |
SQL Diagnostic Manager for SQL Server performance monitoring, alerting, and diagnostics for SQL Server.| | | | | | | |