Process Blocking (SQL Server Blocks)
You can view SQL Server blocking activity from the Process Blocking
node in Server Explorer. By default, the pane reports all SQL Server
blocks that took longer than 10000 milliseconds (10 seconds). You can
change the default value from
Teratrax Performance Agent.
Block Details
The Process Blocking node displays a list of all SQL Server blocks. For detailed information about an individual block, select the
block in the list to view its details in the lower part of the screen.
The details pane shows the SQL code for the blocked process and the last
SQL code executed by the blocking process. You can use this information
among other details in the pane to enhance the concurrency of your server.
What is Process Blocking
SQL Server blocking occurs when one connection (user process or application
process)
places a lock on a table (or a number of rows) and a second connection
attempts to read or modify the data under the lock. Depending on the
type of the lock, this can cause the second connection to wait until the
first connection releases its lock. A blocked connection waits
indefinitely for the blocking connection to release its lock.
The more blocking happens on the server the less concurrency the
system achieves. A certain amount of blocking is unavoidable but too
many blocks for longer periods of time can degrade the performance of
SQL Server.
The combination of two blocked connections where the first is
blocking the second and the second is blocking the first is called a
deadlock. Since deadlocks are not naturally resolved with time, SQL Server
automatically kills one of the processes (Deadlock victim) once it detects a deadlock.
This allows the
other process to continue with its transaction.
Although deadlocks can be caused by two short blocks (In a fraction of a
second), it is the long blocks that usually increase the chances of a
deadlock. Deadlocks are reported in Teratrax Performance Viewer in a
separate node, however, a correlation between the Process Blocking node
and the Deadlocks node is crucial in learning more about the events that
lead to a deadlock.
Blocks Escalating to Deadlocks
The following diagram shows the sequence of events leading to a
deadlock. Consider two applications (A1, A2) accessing two different
table (T1, T2):
Event 1: A1 places a lock on T1 inside its transaction and
continues to execute other statements
Event 2: A2 places a lock on T2 inside its transaction and
continues to execute other statements
Event 3: A1 attempts to place a lock on T2 (Needs to access T2
before it can finish the transaction) but has to wait for A2 to release
its lock
At this point, a block is created since A2 is
blocking A1. The block will be listed in the Process Blocking node if
its duration meets the minimum duration for a block.
Event 4: While A1 is waiting, A2 attempts to place a lock on
T1 (Needs to access T1 before it can finish its own transaction)
SQL Server detects a deadlock when the second
block conflicts with the first block (The two processes
have blocked one another). SQL Server automatically resolves the deadlock by choosing one of
the processes as a deadlock victim and killing it. The deadlock will
be listed in the Deadlocks node. However, the second block (The one
that caused the deadlock) has a low chance of being listed in the
Process Blocking pane since its duration is usually less than 10
seconds (The block ends when SQL Server resolves the deadlock).
|
