Need a blocking script with specific details

  • 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 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

  • you can check where blocked != 0 from master..sysprocesses table.

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

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

  • 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

  • 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

  • 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!

  • 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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply