View locks acquired during query execution (SQL Server)

Query execution plan does not show the locking details by default, is it possible to view the locks, along with the type, acquired during the execution of a query?

is it possible to view the locks, along with the type, acquired during the execution of a query?

Yes, for determining locks,

-
You can use beta_lockinfo by Erland Sommarskog

beta_lockinfo is a stored procedure that provides information about processes and the locks they hold as well their active transactions. beta_lockinfo is designed to gather as much information about a blocking situation as possible, so that you can instantly find the culprit and kill the blocking process if the situation is desperate. Then you can sit back and analyse the output from beta_lockinfo to understand how the blocking situation arose and figure out what actions to take to prevent the situation from reoccurring. The output from beta_lockinfo shows all active process as well as passive processes with locks, which objects they lock, what command they last submitted and which statement they are executing. You also get the query plans for the current statements. Normally, you run beta_lockinfo to look at the output directly, but there is also an archive mode where the data is saved to table. This is not the least useful, if you want someone to send you the output from beta_lockinfo at a site you don't have access to yourself.

-
Another method is to use sp_whoIsActive by Adam Machanic with @get_locks = 1

EXEC sp_WhoIsActive 
@filter = '', 
@filter_type = 'session', 
@not_filter = '', 
@not_filter_type = 'session', 
@show_own_spid = 0, 
@show_system_spids = 0, 
@show_sleeping_spids = 1, 
@get_full_inner_text = 0, 
@get_plans = 1, 
@get_outer_command = 1, 
@get_transaction_info = 0, 
@get_task_info = 1, 
@get_locks = 1,   ----------> 1 = ON (get lock info); 0 = OFF
@get_avg_time = 0, 
@get_additional_info = 0, 
@find_block_leaders = 0, 
@delta_interval = 0, 
@output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]', 
@sort_order = '[start_time] ASC', 
@format_output = 1, 
@destination_table = '', 
@return_schema = 0, 
@schema = NULL, 
@help = 0

StackExchange Database Administrators Q#99317, answer score: 15