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.

Blocking Sessions Report

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.

Reduce blocked sessions wait time

If you experience more than one blocked session on an average day, your site may experience one of the following issues:

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 identifies and resolves SQL Server performance problems before they happen. Learn more > >
Idera WebsiteProductsPurchaseSupportCommunityAbout UsResources Legal