Number of locks acquired during query execution I execute a statement in SSMS like:

select Ident 
from PART_Stamm 
where VerketteterArtikelID = 'D9FA5CEC-E3C5-11E6-B088-F079596E5F58'


There I can show the execution plan. This works perfectly. Now I need to also show the number of locks acquired on table PART_Stamm during the execution of the query above. How can I get this information?
This is the perfect use for extended events.

-- replace Session_id with your spid
CREATE EVENT SESSION [Specific_Locks] ON SERVER 
ADD EVENT sqlserver.lock_acquired(
    WHERE ([sqlserver].[session_id]=(55))),
ADD EVENT sqlserver.lock_escalation(
    WHERE ([sqlserver].[session_id]=(55))),
ADD EVENT sqlserver.lock_released(
    WHERE ([sqlserver].[session_id]=(55))),
ADD EVENT sqlserver.lock_timeout_greater_than_0(
    WHERE ([sqlserver].[session_id]=(55)))
ADD TARGET package0.event_file(SET filename=N'Specific_Locks',max_file_size=(100))
GO

ALTER EVENT SESSION [Specific_Locks] ON SERVER STATE=START
GO

-- run your query

ALTER EVENT SESSION [Specific_Locks] ON SERVER STATE=STOP
GO


This will give you everything your session does, however, you can filter on the object ids for the table and indexes to make it more readable. I included acquired, released, timeout, and escalation as I don't know your environment.

If you want to look at the captured data, there are two built in ways without using outside programs.


Use sys.fn_xe_file_target_read_file() with proper supplied arguments
Use SMSS to open the file target for you and give you a semi-intelligible display (picture below)