In SQL Server / Azure SQL is there a way to see the locks a query produced or wants to produce?

[Analeea]

I'm trying to investigate a lock situation. I know how to see the current locks on DB by querying sys.dm_tran_locks but what I want is to take a specific SQL Query and having a kind of 'analysis' on it to calculate what locks it will generate. Like an execution plan for locks?

It can also be a way where I run a query and afterwards I see what locks were generated, for me it's the same. I just need a way to log/see logs generated since I currently can only see 'real-time' locks being held.

I specifically need to know as many details of those locks as possible: type, mode, object it's holding, etc.

I'm on Azure SQL Database.

[Amritpal]

The easy way is to start a transaction, run the query and then examine sys.dm_tran_locks. But this will only show you locks that are held after the query, typically U or X locks, or S locks in REPEATABLE READ or SERIALIZABLE. eg

if @@trancount > 0 rollback
begin transaction
--run some queries
select *

from sys.dm_tran_locks

where request_session_id = @@spid
rollback

To see the complete history of lock acquisition and release for a statement use XEvents or Profiler to capture the lock:acquired and lock:released events for a session. Note that this trace is extremely verbose so don't attempt to capture it for a whole instance, or on a production server at all.