Number of locks acquired during query execution

Question

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?

Answer 1

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.

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

Answer 2

This is really easy with Adam Machanic's free tool, sp_WhoIsActive.

You have to either catch a query when it's running, or in a transaction. For instance:

USE StackOverflow;

BEGIN TRAN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT COUNT(*)
FROM   dbo.Posts AS p
JOIN dbo.Users AS u
ON u.Id = p.OwnerUserId
WHERE  p.OwnerUserId >= 0;

Then you want to run this command (from another tab, or use EXEC dbo.sp_WhoIsActive @get_locks = 1, @show_own_spid = 1 from the same tab):

EXEC dbo.sp_WhoIsActive @get_locks = 1

If you click on the locks column, you'll get output like this:

<Database name="StackOverflow">
  <Locks>
    <Lock request_mode="S" request_status="GRANT" request_count="1" />
  </Locks>
  <Objects>
    <Object name="Posts" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="S" request_status="GRANT" request_count="1" />
      </Locks>
    </Object>
    <Object name="Users" schema_name="dbo">
      <Locks>
        <Lock resource_type="OBJECT" request_mode="IS" request_status="GRANT" request_count="1" />
        <Lock resource_type="PAGE" page_type="*" index_name="ix_Users_Helper" request_mode="S" request_status="GRANT" request_count="24725" />
      </Locks>
    </Object>
  </Objects>
</Database>

The request_count should tell you the number of locks.