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.