Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extended Events: get table associated with a page or extent lock Expand / Collapse
Author
Message
Posted Friday, April 11, 2014 7:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 5:18 AM
Points: 14, Visits: 57
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

Post #1560915
Posted Thursday, April 17, 2014 4:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 12, 2014 5:18 AM
Points: 14, Visits: 57
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.
Post #1562597
Posted Thursday, April 17, 2014 7:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1562653
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse