April 11, 2014 at 7:39 am
I am using the lock_released event as part of some diagnostics that I am setting up to look at a slow running process that is causing grief for other processes.
What I'd like to know is for locks that have been held over a certain threshold duration, what has been locked, the type of lock, what has locked it, and for how long.
My problem is that if the lock is on a page or an extent I don't know how to retrieve the name of the corresponding table.
I am on SQL2012.
I have set up my extended event session as follows:
CREATE EVENT SESSION [locking_report] ON SERVER
ADD EVENT sqlserver.lock_released(
ACTION (sqlserver.client_app_name
,sqlserver.sql_text
)
WHERE DURATION > 10 /*duration measured in microsecnds*/
)
ADD TARGET package0.asynchronous_file_target
(SET filename = N'c:\temp\XEventSessions\locking_report.xel',
metadatafile = N'c:\temp\XEventSessions\locking_report.xem',
max_file_size=(65536),
max_rollover_files=5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO
The xml that is produced for a page lock is ...
<event name="lock_released" package="sqlserver" timestamp="2014-04-11T07:42:30.081Z">
<data name="resource_type">
<value>6</value>
<text>PAGE</text>
</data>
<data name="mode">
<value>5</value>
<text>X</text>
</data>
<data name="owner_type">
<value>1</value>
<text>Transaction</text>
</data>
<data name="transaction_id">
<value>24997926</value>
</data>
<data name="database_id">
<value>2</value>
</data>
<data name="lockspace_workspace_id">
<value>0x00000002ec424b70</value>
</data>
<data name="lockspace_sub_id">
<value>115</value>
</data>
<data name="lockspace_nest_id">
<value>1</value>
</data>
<data name="resource_0">
<value>192130</value>
</data>
<data name="resource_1">
<value>1</value>
</data>
<data name="resource_2">
<value>0</value>
</data>
<data name="object_id">
<value>0</value>
</data>
<data name="associated_object_id">
<value>422222562066432</value>
</data>
<data name="duration">
<value>0</value>
</data>
<data name="resource_description">
<value />
</data>
<data name="database_name">
<value />
</data>
<action name="sql_text" package="sqlserver">
<value>SELECT top (100) * from SampleTable
</value>
</action>
<action name="client_app_name" package="sqlserver">
<value>Microsoft SQL Server Management Studio - Query</value>
</action>
</event>
I would have suspected the resource_0 field, but on occasion the value her can be larger than the 'int' database type. My other thought is that it could be associated_object_id, but I believe that this relates to something more low level.
Does anyone know for sure?
Is there perhaps a better way to achieve what I want.
Thanks
April 17, 2014 at 4:56 am
In the end I looked for the 'intent' locks being taken on the table and used transactionID to link them to the page and extent level locks taken as part of the same process.
Hope that helps anyone who stumbles on this post with a similar problem.
April 17, 2014 at 7:34 am
The Associated_object_id is not an object ID, it's too big. It's an allocation unit ID (or HoBT). See sys.partitions and/or sys.allocation_units
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply