SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need a blocking script with specific details


Need a blocking script with specific details

Author
Message
gvijaybabu
gvijaybabu
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 199
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
s_osborne2
s_osborne2
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1705 Visits: 2292
Post what you have so far so we can see what you're doing.



MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
george sibbald
george sibbald
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23610 Visits: 13698
you can check where blocked != 0 from master..sysprocesses table.

you can also use profiler now to trap information on blocks.

---------------------------------------------------------------------
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)SSC Guru (94K reputation)

Group: General Forum Members
Points: 94819 Visits: 33011
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
gvijaybabu
gvijaybabu
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 199
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
Jacob Wilkins
Jacob Wilkins
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7192 Visits: 10068
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!
bikram.pattanayak
bikram.pattanayak
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 266
gvijaybabu - Wednesday, July 31, 2013 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_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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search