Use profiler blocked Process report & alert to monitor database Blocking

SQL Server Profiler provides a blocked Process report event, which records blocking tasks that exceed the specified time. With this event, we can record the blocking information of the database.

 

Next let's take a look at how to use blocked processreport:

 

To use blocked Process report, you must configure the threshold and report generation frequency. Use the sp_configure command to configure the blocked process threshold option (in seconds ). By default, no blocked process reports are generated.

 

1. Configure the blocked process threshold:

 

Sp_configure 'showadvanced options', 1;

Go

-- Use reconfigure hint to prevent a restart ofthe SQL Server for changes to take effect

Reconfigure;

Go

-- Set the blocked process threshold value to 10 seconds

Sp_configure 'blockedprocess threshold ', 10;

Go

-- Use reconfigure hint to prevent a restart ofthe SQL Server for changes to take effect

Reconfigure;

Go

 

2. Open profiler and select the blocked Process report event:

 

3. Simulate blocking and open two Query Interfaces to run simultaneously:

 

Begin tran

Update Test Set Name = 'test1'

 

4. In 10 seconds, we will see that the blocking information has been recorded in profiler:

 

5. We can save the trace template as a script and run it regularly on the database, so that we can understand the blocking status of the database. Because the background detection mechanism is the same as the deadlock mechanism, the impact on the database is very small.

 

For more information, see blocked Process report event class: http://msdn.microsoft.com/zh-cn/library/ms191168.aspx

 

In addition, we set alert. If blocking exceeds a certain period of time, it can be sent to DBA.

 

1. Create alert:

 

 

2. Set response (it can be sent to DBA, or the script generated above can be executed to help you capture blocking information in a timely manner)

 

 

Blocking can also create a job to regularly capture blocking status, the statement can refer to: http://blog.csdn.net/smithliu328/article/details/7849290