Database Reference
In-Depth Information
where
TL1.request_session_id <> @@spid
order by
TL1.request_session_id
option (recompile)
Figure
18-2
shows the results of the query. As you can see, it is much easier to understand, and it provides you
with more useful information including statements and execution plans of the currently running sessions. One thing
to keep in mind is that the execution plans obtained from the DMVs in this chapter do not include the actual execution
statistics metrics, such as the actual number of rows returned by operators and the number of their execution.
Figure 18-2.
Joining sys.dm_os_tran_locks with other DMVs
■
You need to run the query in the context of the database involved in the blocking to correctly resolve the object
names. also of importance- is that, for the sessions in which lock requests were granted, SQl and Query plan represent
the currently executed batch, rather than the batch triggered by the original locking request.
Note
The
sys.dm_tran_locks
view returns one row for each active lock request in the system, which can lead to very
large result sets when you run it on busy servers. You can reduce the amount of information and perform a self-join of
this view based on the
resource_description
and
resource_associated_entity_id
columns, and you can identify
the sessions that compete for the same resources. Such an approach allows you to filter out the results and only see
the sessions that are involved in the blocking chains.
Listing 18-2 and Figure
18-3
illustrate the code and query results.
Listing 18-2.
Filtering Out Blocked and Blocking Session Information
select
TL1.resource_type as [Resource Type]
,db_name(TL1.resource_database_id) as [DB Name]
,case TL1.resource_type
when 'OBJECT' then
object_name(TL1.resource_associated_entity_id
,TL1.resource_database_id)
when 'DATABASE' then
'DB'
else
case
when TL1.resource_database_id = db_id()
then
