2

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?

1
  • 1
    Why do you need to know the number of locks? Many locks will be acquired and released during query execution so I'm not sure the value of that information.
    – Dan Guzman
    Sep 22, 2017 at 19:25

2 Answers 2

6

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)

Where to find XE target view data

0
4

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

NUTS

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.