Querying the Lock Acquired Extended Event

In this post, I will show how to query the lock acquired extended event that I used in the previous post. This query summarizes the data by aggregating the count of locks of the same type but preserving the sequence in which they appeared. For this purpose, I used the SQL 2012 windowing function LAG to solve the “island” problem as described by Itzik Ben-Gan in his T-SQL Querying book.

I used the same sample data of the previous post. Next I used the following extend event filtered by the session id I used to run the delete query, as shown below.

 
CREATE EVENT SESSION [lock_acquired] ON SERVER 
ADD EVENT sqlserver.lock_acquired(SET collect_resource_description=(1)
    ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)
    WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(91))))
ADD TARGET package0.event_file(SET filename=N'c:\temp\lock_acquired')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

alter event session [lock_acquired]
on server
state = start;

delete top (1000) child_table
from child_table c
left join parent_table p
on c.parent_id = p.parent_id
where p.parent_id is null

alter event session [lock_acquired]
on server
state = stop;

Then you can run the following query to get the locks acquired

with a as(
SELECT
t.c.value ('@name', 'nvarchar(50)') AS event_name
,t.c.value ('@timestamp', 'datetime2') AS event_time
,t.c.value ('(data[@name="associated_object_id"]/value)[1]', 'numeric(20)') AS associated_object_id
,t.c.value ('(data[@name="resource_0"]/value)[1]', 'bigint') AS resource_0
,t.c.value ('(data[@name="resource_1"]/value)[1]', 'bigint') AS resource_1
,t.c.value ('(data[@name="resource_2"]/value)[1]', 'bigint') AS resource_3
,t.c.value ('(data[@name="mode"]/text)[1]', 'varchar(50)') AS mode
,t.c.value ('(data[@name="resource_type"]/text)[1]', 'varchar(50)') AS resource_type
,t.c.value ('(data[@name="database_id"]/value)[1]', 'int') AS database_id
,t.c.value ('(data[@name="object_id"]/value)[1]', 'int') AS object_id
,t.c.value ('(action[@name="attach_activity_id"]/value)[1]', 'varchar(200)') AS attach_activity_id
FROM (
SELECT
    CAST(event_data AS XML) AS event_xml
 FROM sys.fn_xe_file_target_read_file
 (
 'C:\temp\lock_acquired_0_131015242967150000.xel'
 , null
 , null
 , null
)) target_read_file
CROSS APPLY event_xml.nodes ('//event') AS t (c)
where t.c.value ('@name', 'nvarchar(50)') = 'lock_acquired'
and t.c.value ('(data[@name="associated_object_id"]/value)[1]', 'numeric(20)') > 0
and t.c.value ('(data[@name="database_id"]/value)[1]', 'int') <> 2
) 
select
event_time
,object_name(isnull(p.object_id,a.object_id),a.database_id) object_name
,resource_type
,mode
,count(*) count
into #temp
from a
left join sys.partitions p
on a.associated_object_id = p.hobt_id
group by
event_time
,object_name(isnull(p.object_id,a.object_id),a.database_id)
,resource_type
,mode

select object_name, resource_type, mode, count from #temp

You will notice there are about 200+ lines that may be difficult to read and could be solved by the “island” task that Itzik describes as “Identifying islands means returning the ranges of consecutive values.” Which are shown below. We want the consecutive S locks of the parent table as well as the U locks of the child table aggregated preserving the sequence

The SQL 2012 window function LAG did the trick

 
with a as(
select * 
,row_number() over(
order by
event_time
,object_name
,resource_type
,mode
) row_num
from #temp
), b as(
select *
,case when object_name + resource_type + mode <>
lag(object_name + resource_type + mode) over(
order by
row_num
) 
or
lag(object_name + resource_type + mode) over(
order by
row_num
) is null
then 1
else 0
end grp1
from a
), c as(
select *
,sum(grp1) over(
order by row_num
) grp2
from b
)
select 
object_name
,resource_type
,mode
,sum(count) count
from c
group by
grp2
,object_name
,resource_type
,mode
order by
grp2
,object_name
,resource_type
,mode