Find all Locks and Blocks on a Database or Server – Point in time

On one of our Databases I noticed frequent blocks resulting web page timeouts, had to analyze what is resulting these blocks and which tables are major contributors.
SP_WHO2 is the easy way to find the processes which are getting blocked and which is causing the blocking. DBCC inputbuffer(@@SP_ID) goes hand in hand to find the actual query. I find the Procedures which are creating the problem but still I am not very happy with the data I have.

I found following SQL Query which will list all the locks acquired on given database (Modify the filters to get the locks on all database or eliminate Shared Locks etc…), it helped me to find the Indexes and tables which are responsible for blocking.

DECLARE @DatabaseName varchar(60)
set @DatabaseName = DB_NAME() -- Chagne it to required Database Name 

SELECT TRL.resource_type AS Resource_Type 
      ,TRL.resource_description AS Resource_Disc 
      ,TRL.request_mode AS Request_Mode 
      ,TRL.request_type AS Request_Type 
      ,TRL.request_status AS Request_Status 
      ,COALESCE(OBJ.name, PAROBJ.name) AS [ObjectName] 
      ,TRL.request_owner_type AS Request_Owner_Type 
      ,TAT.[name] AS Transaction_Name 
      ,TAT.transaction_begin_time AS Transaction_Begin_Time
      ,DATEDIFF(ss, TAT.transaction_begin_time, GETDATE()) AS Transaction_Duraation
      ,ES.session_id AS S_Id 
      ,ES.login_name AS Login_Name 
      ,PARIDX.name AS IndexName 
      ,ES.host_name AS HostName 
      ,ES.program_name AS ProgramName 
FROM sys.dm_tran_locks AS TRL 
     JOIN sys.dm_exec_sessions AS ES ON TRL.request_session_id = ES.session_id 
     LEFT JOIN sys.dm_tran_active_transactions AS TAT ON TRL.request_owner_id = TAT.transaction_id AND TRL.request_owner_type = 'TRANSACTION' 
     LEFT JOIN sys.objects AS OBJ ON TRL.resource_associated_entity_id = OBJ.object_id AND TRL.resource_type = 'OBJECT' 
     LEFT JOIN sys.partitions AS PAR ON TRL.resource_associated_entity_id = PAR.hobt_id AND TRL.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') 
     LEFT JOIN sys.objects AS PAROBJ ON PAR.object_id = PAROBJ.object_id 
     LEFT JOIN sys.indexes AS PARIDX ON PAR.object_id = PARIDX.object_id AND PAR.index_id = PARIDX.index_id 
WHERE TRL.resource_database_id  = DB_ID(@DatabaseName) -- Remove this condition to see all Locks on the Server
-- AND TRL.request_mode <> 'S' -- This will avoid Shared locks which does not block any other processes 
ORDER BY 1, 3, 4, 5, 6 ,ES.login_name;


---- Other Options 
--SP_WHO2 
--DBCC inputbuffer(51)
Advertisements
This entry was posted in Performance, Productivity, Script, SQL and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s