Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How can i get the locks used by the SQL Server to execute my Stored Procedure.

I'm having stored procedure which invloves more than 5 tables.
How can i know what all locks applied on each table while executing my sql Stored procedure.

Thanks in Advance..
Posted

1 solution

u can use this query to check the locks...
SQL
SELECT db.name DBName, tl.request_session_id, wt.blocking_session_id,                  
       OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,tl.resource_type,
       h1.TEXT AS RequestingText, h2.TEXT AS BlockingTest, tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
 
Share this answer
 
v2
Comments
Raja Sekhar S 30-Sep-13 5:31am    
Did you got what you are looking for..?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900