3.5 BlockedProcesses

Use this Knowledge Script to monitor the number of SQL Server processes that are queued for longer than the period of time you specify. You can set a threshold to determine how long a process can be in queue before it is considered blocked. This script raises an event when the number of blocked processes exceeds a threshold you specify.

NOTE:To run this Knowledge Script, you need public and view server state SQL Server permissions. If you do not have these permissions, the Knowledge Script does not display any error, but the data returned is not complete. To get complete data, you must have these permissions.

Resource Object

SQL Server instance

Default Schedule

The default interval for this script is Once every hour.

Setting Parameter Values

Set the following parameters as needed:

Description

How to Set It

General Settings

Job Failure Notification

Raise event if job fails unexpectedly?

Select Yes to raise an event if the SQLServer_BlockedProcess job fails unexpectedly. The default is Yes.

Event severity when job fails unexpectedly

Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the job fails unexpectedly. The default is 5.

Raise event if SQL Server login fails?

Select Yes to raise an event if login to SQL Sever fails. The default is Yes.

Event severity when SQL Server login fails

Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the login to SQL server fails. The default is 15.

Additional Settings

 

Event Details

 

Event detail format

Select the format in which you want to display the event detail. You can select from HTML Table or Plain Text. The default is HTML Table.

Authentication

Select the authentication method that you want to use to access SQL Server. You can either select Windows Authentication or SQL Server Authentication. The default is Windows Authentication.

User name

Specify the Windows or SQL Server user name that you want to use to access SQL Server. You can specify multiple users separated by a comma. The default is none.

For more information on specifying user name, see Specifying the User Name in the Knowledge Script in the Management Guide.

For more information on specifying user name, see Section 2.7.3, Specifying the User Name in the Knowledge Script.

Monitor Blocked Process

Include detail report in data points?

Select Yes to include a detail report in the data points collected for charts and reports. The default is unselected.

Number of blocked processes to include in report

Specify the number of processes to display in the report pane of the console. The default is 20 blocked processes. Enter 0 to display all blocked processes.

Include the T-SQL statement for each blocked process?

Select Yes to include the T-SQL statement associated with each blocked process. The default is unselected.

Timeout

Specify the number of seconds that the Knowledge Script should wait to get the T-SQL statement before raising a timeout exception. The default is 30 seconds.

Event Notification

Raise event if number of blocked processes exceeds threshold?

Select Yes to raise an event if the number of blocked processes exceeds the threshold. The default is Yes.

Event severity when number of blocked processes exceeds threshold

Set the event severity level, from 1 to 40, to indicate the importance of an event in which the number of blocked processes exceeds the threshold. The default is 5.

Threshold -- Maximum number of blocked processes

Specify the maximum number of processes that can be blocked before an event is raised. The default is 5 blocked processes.

Threshold -- Maximum waiting time in queue

Specify the maximum length of time a process can be queued before it is considered a blocked process. The default is 500 milliseconds.

Data Collection

Collect data for total number of blocked processes?

Select Yes to collect data for charts and reports. If enabled, data collection returns the total number of blocked processes. The default is unselected.

Custom data stream legend

Specify a custom data stream legend to append with the default data legend for the job that is visible in the console. You can specify a maximum of 128 alphanumeric characters in a string, including special characters. The default is none.