SP to Identify SP's That are causing locks? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SP to Identify SP’s That are causing locks?

Friends I have got lots of tips and helps from This forum.
Hope you gurus can throw some light into my following issue also
Is there a tool or an Sp that can identify other Sp’s or tables that causes a System to Hang state.
This phenomenon of system hang occurs rarely and is very hard to simulate.
So Once the system is on a hang state,and If I run an SP or a tool it should return me the name of the SP or tables that care causing the system to a Hang state. Any thoughts in this regard is highly appreciated
Regards
Anil Never Give Up
Have a look at http://www.sommarskog.se/sqlutil/aba_lockinfo.html Roji. P. Thomas
Microsoft SQL Server MVP
http://toponewithties.blogspot.com

Thanks Roji For your Immediate Response.
I will try to work it out
Thanks
Anil Never Give Up
Dear Roji That link was really useful.
Now I am able to locate the Sp’s that are causing blocks.
Thank you
Anil Never Give Up
And this gives you the objects that are involved in the locking process… I created for my use. Guess might be useful for you too…
IF object_id(‘SP_FindBlockingObjects’) IS NOT NULL
BEGIN
RAISERROR (‘Procedure SP_FindBlockingObjects dropped’,0,1)
DROP PROCEDURE SP_FindBlockingObjects
END
Go CREATE PROCEDURE SP_FindBlockingObjects
AS
SET NOCOUNT ON IF object_id(‘tempdb..##BlockingInfo’) IS NULL
BEGIN
SELECT rsc_objid [Object_ID]
,object_name(rsc_objid) [Object Name]
,CASE rsc_type WHEN 1 THEN ‘Not Used Resource’
WHEN 2 THEN ‘Database’
WHEN 3 THEN ‘File’
WHEN 4 THEN ‘Index’
WHEN 5 THEN ‘Table’
WHEN 6 THEN ‘Page’
WHEN 7 THEN ‘Key’
WHEN 8 THEN ‘Extent’
WHEN 9 THEN ‘RID (Row ID)’
WHEN 10 THEN ‘Application’
END [Object Type]
,CASE req_mode WHEN 0 THEN ‘No Access Granted’
WHEN 1 THEN ‘Schema stability’
WHEN 2 THEN ‘Schema modification’
WHEN 3 THEN ‘Shared’
WHEN 4 THEN ‘Update’
WHEN 5 THEN ‘Exclusive’
WHEN 6 THEN ‘Intent Shared’
WHEN 7 THEN ‘Intent Update’
WHEN 8 THEN ‘Intent Exclusive’
WHEN 9 THEN ‘Shared Intent Update’
WHEN 10 THEN ‘Shared Intent Exclusive’
WHEN 11 THEN ‘Update Intent Exclusive’
WHEN 12 THEN ‘Used by bulk operations’
WHEN 13 THEN ‘Serializable range scan’
WHEN 14 THEN ‘Serializable update scan’
WHEN 15 THEN ‘Test ranges before inserting a new key’
WHEN 16 THEN ‘overlap of RangeI_N and S locks’
WHEN 17 THEN ‘overlap of RangeI_N and U locks’
WHEN 18 THEN ‘overlap of RangeI_N and X locks’
WHEN 19 THEN ‘overlap of RangeI_N and RangeS_S locks’
WHEN 20 THEN ‘overlap of RangeI_N and RangeS_U locks’
WHEN 21 THEN ‘Updating a key in a range’
END [Lock Requested]
,CASE req_status WHEN 1 THEN ‘Granted’
WHEN 2 THEN ‘Converting’
WHEN 3 THEN ‘Waiting’
END [Request Status]
,CASE req_ownertype WHEN 1 THEN ‘Transaction’
WHEN 2 THEN ‘Cursor’
WHEN 3 THEN ‘Session’
WHEN 4 THEN ‘ExSession’
END [Object Associated with Lock]
,spid [SPID]
,blocked [Blocked By]
,waitresource [Waiting Resource]
,cpu [Total CPU Time Consumed]
,waittime [Waiting Time]
,physical_io [Total Physical_IO Wait Time]
,open_tran [NO of Transactions Opened]
,program_name [Program Name]
,cmd [Command Executed]
,[No_of_Blocked_Process]
,[Time of Observation] = GETDATE()
INTO ##BlockingInfo
FROM master..syslockinfo A
JOIN
(SELECT spid
,blocked
,waitresource
,cpu
,waittime
,physical_io
,open_tran
,program_name
,cmd
,dbid
,count(*) [No_of_Blocked_Process]
FROM master..sysprocesses
WHERE blocked <> 0
AND dbid = 7
GROUP BY spid
,blocked
,waitresource
,cpu
,waittime
,physical_io
,open_tran
,program_name
,cmd
,dbid ) B
ON B.spid = A.req_spid
AND A.rsc_dbid = B.dbid
END
ELSE
BEGIN
INSERT INTO ##BlockingInfo
SELECT * FROM
(SELECT [Object_ID] = rsc_objid
,[Object Name] = object_name(rsc_objid)
,[Object Type] = CASE rsc_type WHEN 1 THEN ‘Not Used Resource’
WHEN 2 THEN ‘Database’
WHEN 3 THEN ‘File’
WHEN 4 THEN ‘Index’
WHEN 5 THEN ‘Table’
WHEN 6 THEN ‘Page’
WHEN 7 THEN ‘Key’
WHEN 8 THEN ‘Extent’
WHEN 9 THEN ‘RID (Row ID)’
WHEN 10 THEN ‘Application’
END
,[Lock Requested] = CASE req_mode WHEN 0 THEN ‘No Access Granted’
WHEN 1 THEN ‘Schema stability’
WHEN 2 THEN ‘Schema modification’
WHEN 3 THEN ‘Shared’
WHEN 4 THEN ‘Update’
WHEN 5 THEN ‘Exclusive’
WHEN 6 THEN ‘Intent Shared’
WHEN 7 THEN ‘Intent Update’
WHEN 8 THEN ‘Intent Exclusive’
WHEN 9 THEN ‘Shared Intent Update’
WHEN 10 THEN ‘Shared Intent Exclusive’
WHEN 11 THEN ‘Update Intent Exclusive’
WHEN 12 THEN ‘Used by bulk operations’
WHEN 13 THEN ‘Serializable range scan’
WHEN 14 THEN ‘Serializable update scan’
WHEN 15 THEN ‘Test ranges before inserting a new key’
WHEN 16 THEN ‘overlap of RangeI_N and S locks’
WHEN 17 THEN ‘overlap of RangeI_N and U locks’
WHEN 18 THEN ‘overlap of RangeI_N and X locks’
WHEN 19 THEN ‘overlap of RangeI_N and RangeS_S locks’
WHEN 20 THEN ‘overlap of RangeI_N and RangeS_U locks’
WHEN 21 THEN ‘Updating a key in a range’
END
,[Request Status] = CASE req_status WHEN 1 THEN ‘Granted’
WHEN 2 THEN ‘Converting’
WHEN 3 THEN ‘Waiting’
END
,[Object Associated with Lock] = CASE req_ownertype WHEN 1 THEN ‘Transaction’
WHEN 2 THEN ‘Cursor’
WHEN 3 THEN ‘Session’
WHEN 4 THEN ‘ExSession’
END
,[SPID] = spid
,[Blocked By] = blocked
,[Waiting Resource] = waitresource
,[Total CPU Time Consumed] = cpu
,[Waiting Time] = waittime
,[Total Physical_IO Wait Time] = physical_io
,[NO of Transactions Opened] = open_tran
,[Program Name] = program_name
,[Command Executed] = cmd
,[No_of_Blocked_Process]
,[Time of Observation] = GETDATE()
FROM master..syslockinfo A
JOIN (SELECT spid
,blocked
,waitresource
,cpu
,waittime
,physical_io
,open_tran
,program_name
,cmd
,dbid
,count(*) [No_of_Blocked_Process]
FROM master..sysprocesses
WHERE blocked <> 0
AND dbid = 7
GROUP BY spid
,blocked
,waitresource
,cpu
,waittime
,physical_io
,open_tran
,program_name
,cmd
,dbid ) B
ON B.spid = A.req_spid
AND A.rsc_dbid = B.dbid) Z
WHERE NOT EXISTS (SELECT 1 FROM ##BlockingInfo Y
WHERE Z.[Object_ID] = Y.[Object_ID]
AND Z.[Object Type] = Y.[Object Type]
AND Z.[Lock Requested] = Y.[Lock Requested]
AND Z.[Request Status] = Y.[Request Status]
AND Z.[Object Associated with Lock] = Y.[Object Associated with Lock]
AND Z.[SPID] = Y.[SPID]
AND Z.[Program Name] = Y.[Program Name]
)
END SELECT * FROM ##BlockingInfo
RETURN
Go
RAISERROR (‘Procedure SP_FindBlockingObjects created’,0,1) Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"
]]>