Databases Reference
In-Depth Information
To identify the blocked and blocking requests, we executed a query that joined the following
dynamic management views and dynamic management functions:
F
dm_exec_requests
F
dm_exec_sessions
F
dm_exec_connections
F
dm_exec_sql_text
The DMVs in the query are joined using
session_id
. By using
CROSS
APPLY
on the dynamic
management function by passing the
sql_handle
value of the most recent request of a
session, we retrieved the T-SQL queries for both blocked query and blocking query as well.
The resulting columns are very helpful in detecting the source of blocking queries.
We then fictitiously assumed that the transaction that was started from Connection-1 was
blocking the other transactions and we killed the process of that transaction by passing its
session_id
value(
56
) to the
KILL
command.
As soon as the transaction with
session_id
value
56
was killed, the update was successful
and the transaction in Connection-2 was committed.
Detecting deadlocks with SQL Server
Profiler
Let's suppose you are a database administrator. One of your colleagues reports to you about
frequent deadlocks occurring in the database due to inefficient application code and asks you
to investigate such deadlocks and to analyze when they occur. As a DBA you are required to
detect such situations and find out the queries that are the culprits behind these deadlocks.
Deadlock is a state of blockage that occurs when two or more transactions are blocked by
one another in such a way that, in order to complete its transaction, each transaction waits to
acquire a lock on the resource that the other one has locked. In this state, each transaction
waits for the other one to be finished, in order to be able to complete its own transaction. This
results in endless blocking and neither transaction can be completed. The following sample
diagram represents the deadlock in action:
Search WWH ::
Custom Search