Database Reference
In-Depth Information
Figure 20-19.
Entering the actions to be performed when the alert is triggered
4.
The Blocking Analysis job is selected to automatically collect the blocking information.
5.
Once you've finished entering all the information, click OK to create the SQL Server alert.
The SQL Server alert will be created in the enabled state to perform the intended tasks.
6.
Ensure that the SQL Server Agent is running.
Together, the SQL Server alert and the job will automate the blocking detection and the information collection
process. This automatic collection of the blocking information will ensure that a good amount of the blocking
information will be available whenever the system gets into a massive blocking state.
Summary
Even though blocking is inevitable and is in fact essential to maintain isolation among transactions, it can sometimes
adversely affect database concurrency. In a multiuser database application, you must minimize blocking among
concurrent transactions.
SQL Server provides different techniques to avoid/reduce blocking, and a database application should take
advantage of these techniques to scale linearly as the number of database users increases. When an application faces
a high degree of blocking, you can collect the relevant blocking information using various tools to understand the root
cause of the blocking. The next step is to use an appropriate technique to either avoid or reduce blocking.
Blocking can not only hurt concurrency but lead to an abrupt termination of a database request in the case of
mutual blocking between processes or even within a process. We will cover this event, known as a
deadlock
, in the
next chapter.
