how to find order of the lock taken and released.

Question

how to find order of the lock taken and released.

rajemessage 14195

Hall of Fame

Points: 3315
More actions
February 16, 2015 at 10:52 pm

#314680

hi,
Q1) i have express edition [advance] of sqlserver 2008 r2 , is it possible to trace every event with out using profiler as u know it does not ship with it.
Basically i want to see how locks are taken and released in each isolation level when query is executed.
i am using
SELECT resource_database_id, t.resource_type , t.resource_associated_entity_id,partitions.index_id,blocking_session_id,
indexes.name as index_name,o.name,
t.request_status, t.request_mode,t.request_session_id,
t.resource_description
,s.total_elapsed_time,s.start_time, convert(varchar ,getdate() - s.start_time, 114)
,s.wait_type,s.wait_time
,s.last_wait_type,s.lock_timeout,s.row_count
,s.*,o.*,t.*
FROM sys.dm_tran_locks t
LEFT JOIN sys.objects o on t.resource_associated_entity_id=o.object_id
LEFT JOIN sys.dm_exec_requests s ON s.session_id=t.request_session_id
left join sys.partitions on partitions.hobt_id = t.resource_associated_entity_id
left join sys.indexes on indexes.object_id = partitions.object_id and indexes.index_id = partitions.index_id
WHERE s.session_id in (51,52) ORDER BY s.total_elapsed_time
Q2) but i am not sure of the order becuase total_elapsed_time is same for many locks
like
resource_typeresource_associated_entity_idrequest_session_idrequest_modeindex_namenameresource_description
DATABASE051SNULLNULL
OBJECT209427886651Sch-SNULLwt
OBJECT209427886652IXNULLwt
PAGE7205759715088793652IXPK_wtNULL1:204815
KEY7205759715088793652XPK_wtNULL(4fb98fd9bfad)
DATABASE052SNULLNULL
yours sincerley

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can use the server-side trace feature. Express may not have the profiler GUI, but it's still got the trace feature. You'll just have to write up your own trace definition using sp_trace_create and sp_trace_setevent.

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

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

Grant Fritchey SSC Guru Points: 398926 More actions · Answer 2

Or extended events. You can also use those in 2008 and greater for monitoring.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 3

thank u, i am trying sqltrace, but my second question is unanswered.

yorus sinclery.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Question 2 isn't a question, so it's kinda hard to answer.

Trace or extended events will let you see the order that locks are acquired and released.

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

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

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 5

hi,

i am using sql trace but following link says

https://msdn.microsoft.com/en-IN/library/ms186265(v=sql.105).aspx

"Lock mode of the lock acquired. This column is not populated by the Lock:Released event"

and the col is "mode"

please tel me how to get lock:released loged in sqltrace and also i could not find the where it shows wether the

lock is "granted" or in "wait"

yours sincerely.