July 23, 2013 at 9:23 am
Hello All,
I am looking for a script that will give me these details.
1) spid
2) Total number of spids it is blocking directly or indirectly.
3) blocking Query
4) Login name who ran the Query.
5) how long the query has been running for.
I am able to get all the info except "total numberof SPIDs it is blocking directly or indirectly"
Can somebody please help me with this, that will be very helpful.
thanks in advance.
regards
VB
July 23, 2013 at 10:20 am
Post what you have so far so we can see what you're doing.
July 23, 2013 at 10:37 am
you can check where blocked != 0 from master..sysprocesses table.
you can also use profiler now to trap information on blocks.
---------------------------------------------------------------------
July 24, 2013 at 5:03 am
Pretty much all of what you're looking for is available in sys.dm_exec_requests dynamic management view. Take a look there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 31, 2013 at 4:49 am
Hi All,
This is the query I have
SELECT
[Head Blocker SPID] = s.session_id,
[Number of Statements Blocked] = count(r2.blocking_session_id),
[Head Blocker SQL Statement] =(
SELECT text AS [text()]
FROM sys.dm_exec_sql_text(p.sql_handle)
FOR XML PATH(''), TYPE
),
[Head Blocker Run Time (seconds)] = (datediff(second, s.last_request_start_time, getdate())),
[Source Host Name] = ISNULL(s.host_name, N''),
[Login Name] = s.login_name
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
WHERE r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL)
GROUP BY s.session_id,s.login_name,s.host_name,s.last_request_start_time,p.sql_handle
Can somebody please help me with one thing.. in this query I am able to get all the spids that are "DIRECTLY" being blocked. But I need to tweak it in a way that this will give me all the spids which are directly or indirectly blocked for eg – If SPID is blocking SPIDs 56,74 and 56 is blocking 83,34 then the last column(No. of statements it is blocking directly or indirectly) should give ‘4’.
Thanks in advance.
VJ
August 1, 2013 at 11:42 am
Ok, this is not all that pretty, but it at least returns the right values. I'm sure there's a more efficient way of acquiring them, though. It creates a temporary table that eventually contains a row for each Blocking SPID/Blocked SPID combination, including "indirect" blocks, and then counts them per blocking SPID. Hopefully something like this (or a more efficient suggestion from someone else) will help you get what you want.
SELECT Blocked AS Blocker, spid as Blocked INTO #BlockedSpids FROM master.dbo.sysprocesses
WHERE blocked>0
WHILE EXISTS (SELECT Headblocker.Blocker,Tailblocked.Blocked FROM #BlockedSpids AS HeadBlocker
inner join #BlockedSpids AS Tailblocked
ON Headblocker.Blocked=Tailblocked.blocker
WHERE NOT EXISTS (SELECT * FROM #BlockedSpids AS cmpr WHERE Headblocker.blocker=cmpr.blocker and Tailblocked.blocked=cmpr.blocked)
)
BEGIN
INSERT into #BlockedSpids
SELECT distinct Headblocker.Blocker,Tailblocked.Blocked FROM #BlockedSpids AS HeadBlocker
inner join #BlockedSpids AS Tailblocked
ON Headblocker.Blocked=Tailblocked.blocker
WHERE NOT EXISTS (SELECT * FROM #BlockedSpids AS cmpr WHERE Headblocker.blocker=cmpr.blocker and Tailblocked.blocked=cmpr.blocked)
END
SELECT Blocker, COUNT(Blocked) AS AllBlocked FROM #BlockedSpids
GROUP BY Blocker
I hope this helps!
March 22, 2017 at 11:11 pm
gvijaybabu - Wednesday, July 31, 2013 4:49 AMHi All, This is the query I have SELECT [Head Blocker SPID] = s.session_id, [Number of Statements Blocked] = count(r2.blocking_session_id), [Head Blocker SQL Statement] =(SELECT text AS [text()]FROM sys.dm_exec_sql_text(p.sql_handle)FOR XML PATH(''), TYPE), [Head Blocker Run Time (seconds)] = (datediff(second, s.last_request_start_time, getdate())), [Source Host Name] = ISNULL(s.host_name, N''), [Login Name] = s.login_name FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)LEFT OUTER JOIN( SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_numFROM sys.dm_os_waiting_tasks) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)WHERE r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) GROUP BY s.session_id,s.login_name,s.host_name,s.last_request_start_time,p.sql_handleCan somebody please help me with one thing.. in this query I am able to get all the spids that are "DIRECTLY" being blocked. But I need to tweak it in a way that this will give me all the spids which are directly or indirectly blocked for eg – If SPID is blocking SPIDs 56,74 and 56 is blocking 83,34 then the last column(No. of statements it is blocking directly or indirectly) should give ‘4’. Thanks in advance. VJ
Hi Mr. Vijay,
The script you provided is a nice one. If you find the solution to your problem please post it here..
Regards
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply