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)