How to Use sys.dm_tran_locks to find requests waiting for an exclusive lock

Users reported  an application delay on one of the queries. Upon analysis I noticed a transaction lock request on CONVERT. The  DMV sys.dm_tran_locks reported the information I needed to troubleshoot the delay

An example of a typical scenario is where two requests have a shared (S) lock on the same row. Then one of the requests will attempt to upgrade to an exclusive (X) lock. As there is already an existing Shared (S) Lock – which is incompatible with Exclusive (X) Locks – the lock request  changes to CONVERT and remains on CONVERT until the other Shared (S) Lock is released

 Use the DMV sys.dm_tran_locks to identify Exclusive Locks and attempts on obtaining an Exclusive Lock

 What is the current status ? check the request_status  column

GRANTED = locked request is granted state

WAIT = a request is waiting for a lock type

CONVERT = request already granted a lock but attempting escalate

Use this query to obtain information on existing locks broken down per database.

  select resource_type,db_name(resource_database_id),
resource_associated_entity_id,
request_mode,request_type,
request_status
from sys.dm_tran_locks