Database Reference
In-Depth Information
Automation to Detect and Collect Blocking Information
In addition to capturing information using extended events, you can automate the process of detecting a blocking
condition and collecting the relevant information using SQL Server Agent. SQL Server provides the Performance
Monitor counters shown in Table
20-1
to track the amount of wait time.
Table 20-1.
Performance Monitor Counters
Object
Counter
Instance
Description
SQLServer:Locks
(For SOL
Server named instance
MSSOL$<InstanceName>:Locks)
Average Wait Time(ms)
_Total
Average amount of wait time for each
lock that resulted in a wait
Lock Wait Time (ms)
_Total
Total wait time for locks in the last
second
You can create a combination of SQL Server alerts and jobs to automate the following process:
1.
Determine when the average amount of wait time exceeds an acceptable amount of
blocking using the
Average Wait Time (ms)
counter. Based on your preferences, you can
use the
Lock Wait Time (ms)
counter instead.
2.
Once you've established the minimum wait, set
Blocked Process Threshold
. When the
average wait time exceeds the limit, notify the SQL Server DBA of the blocking situation
through e-mail.
3.
Automatically collect the blocking information using the blocker script or a trace that
relies on the Blocked Process report for a certain period of time.
To set up the Blocked Process report to run automatically, first create the SQL Server job, called Blocking
Analysis, so that it can be used by the SQL Server alert you'll create later. You can create this SQL Server job from SQL
Server Management Studio to collect blocking information by following these steps:
1.
Generate an extended events script (as detailed in Chapter 6) using the
blocked_process_report
event.
2.
Run the script to create the session on the server, but don't start it yet.
3.
In Management Studio, expand the server by selecting
<ServerName>
➤
SQL Server
Agent
➤
Jobs. Finally, right-click and select New Job.
4.
On the General page of the New Job dialog box, enter the job name and other details.
5.
On the Steps page, click New and enter the command to start and stop the session through
T-SQL, as shown in Figure
20-17
.
