Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need a blocking script with specific details Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 9:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:10 AM
Points: 2, 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
Post #1476657
Posted Tuesday, July 23, 2013 10:20 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 8:56 AM
Points: 710, Visits: 2,205
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
Post #1476706
Posted Tuesday, July 23, 2013 10:37 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 5,881, Visits: 13,016
you can check where blocked != 0 from master..sysprocesses table.

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


---------------------------------------------------------------------

Post #1476713
Posted Wednesday, July 24, 2013 5:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 13,925, Visits: 28,317
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1476987
Posted Wednesday, July 31, 2013 4:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:10 AM
Points: 2, 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
Post #1479386
Posted Thursday, August 1, 2013 11:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:01 AM
Points: 169, Visits: 456
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!
Post #1480055
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse