February 23, 2024 at 8:01 pm
Hi All,
We have an application that spawns multiple spid's, runs multiple multiple batches , multiple txns. While troubleshooting a BLOCKING, initially thought to trace one spid and collect the sql statements for that session. But eventually, dev team and we saw multiple spid's are spawned as part of that process and even multiple databases are involved(in this case two databases). how to troubleshoot such blocking problem to tell the cause of the blocking, especially when multiple spids & db's are involved? I find it very difficult to tell a story story. how do you approach such blocking problem when they have long running processes and multiple spids are involved? What questions we should ask ourselves and app team to debug such perf issues? Looking for general approach.
Thank you.
Regards,
Sam
February 24, 2024 at 3:19 pm
see if this script can help you visualize the actual blocker(s).
this is what I use, I am sure I picked it up here on SSC and further enhanced it. for me, it shows who is the head blocker, how long it has been blocking in hh;mm;ss, and who is essentially waiting, so I can zoom in on the specific session, and see what kind of locks it has taken, and be able to grab the execution plan if needed.
--desc: produces a tree like structure of current blocking tree with the main blockers marked as HEAD:
SET NOCOUNT ON
GO
SELECT
ElapsedTime = dt.Days + ':' + dt.Hours + ':' + dt.Minutes + ':' + dt.Seconds,
[R].[spid],
[R].[blocked],
[R].[nt_username],
[R].[hostname],
[R].[program_name],
QUOTENAME(OBJECT_SCHEMA_NAME(T.[objectid],T.[dbid]))
+ '.'
+ QUOTENAME(OBJECT_NAME(T.[objectid],T.[dbid])) AS ObjectName,
REPLACE (REPLACE ([T].[text], CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM [sys].[sysprocesses] R
CROSS APPLY [sys].[dm_exec_sql_text]([R].[sql_handle]) T
CROSS APPLY(SELECT [Days] = RIGHT('000' + CONVERT(VARCHAR(128),DATEPART(DAY,GETDATE() - R.[last_batch])-1),2),
[Hours] = RIGHT('000' + CONVERT(VARCHAR(128),DATEPART(HOUR,GETDATE() - R.[last_batch])),2),
[Minutes] = RIGHT('000' + CONVERT(VARCHAR(128),DATEPART(MINUTE,GETDATE() - R.[last_batch])),2),
[Seconds] = RIGHT('000' +CONVERT(VARCHAR(128),DATEPART(SECOND,GETDATE() - R.[last_batch])),2)
) dt
GO
WITH BLOCKERS (ElapsedTime,SPID, BLOCKED, nt_username,hostname,[program_name],ObjectName,LEVEL, BATCH)
AS
(
SELECT R.ElapsedTime,
[R].[spid],
[R].[blocked],
[R].[nt_username],
[R].[hostname],
[R].[program_name],
R.ObjectName,
CAST (REPLICATE ('0', 4-LEN (CAST ([R].[spid] AS VARCHAR))) + CAST ([R].[spid] AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
[R].[BATCH] FROM #T R
WHERE ([R].[blocked] = 0 OR [R].[blocked] = [R].[spid])
AND EXISTS (SELECT * FROM #T R2 WHERE [R2].[blocked] = [R].[spid] AND [R2].[blocked] <> [R2].[spid])
UNION ALL
SELECT R.ElapsedTime,[R].[spid],
[R].[blocked],
R.[nt_username],
[R].[hostname],
[R].[program_name],
R.ObjectName,
CAST ([BLOCKERS].[LEVEL] + RIGHT (CAST ((1000 + [R].[spid]) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
[R].[BATCH] FROM #T AS R
INNER JOIN BLOCKERS ON [R].[blocked] = [BLOCKERS].[SPID] WHERE [R].[blocked] > 0 AND [R].[blocked] <> [R].[spid]
)
SELECT ElapsedTime,[BLOCKERS].[SPID],[BLOCKERS].[nt_username],[hostname],[program_name],ISNULL(ObjectName,'==Inline SQL Text ==') AS ObjectName,N' ' + REPLICATE (N'| ', LEN ([BLOCKERS].[LEVEL])/4 - 1) +
CASE WHEN (LEN([BLOCKERS].[LEVEL])/4 - 1) = 0
THEN 'HEAD - '
ELSE '|------ ' END
+ CAST ([BLOCKERS].[SPID] AS NVARCHAR (10)) + N' ' + [BLOCKERS].[BATCH] AS BLOCKING_TREE
FROM BLOCKERS ORDER BY [BLOCKERS].[LEVEL] ASC
GO
DROP TABLE #T
GO
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply