Blocking Chain (SQL Spackle)

  • SQLDBAPerth

    Old Hand

    Points: 393

    Comments posted to this topic are about the item Blocking Chain (SQL Spackle)

  • i_moura71

    Valued Member

    Points: 65

    Hi,

    First of all, this is a good article.

    Yet, in the Sql 2014 CTP1 the behavior it's quite diferent. Altough the engine detects a conflict, the engine itself deals with it and don't mark blocking sessions.

    Please try it.... 🙂

    Regards,

  • i_moura71

    Valued Member

    Points: 65

    I guys,

    In Sql 2014 CTP1, I'd perform the operation in another sequence, so the behaviour, of course, was diferent.

    If you perform step by step, like the article, you have the same results.

    Regards,

  • Divine Flame

    SSCoach

    Points: 15941

    Nice article Paul.

    Built-in activity monitor too can be used to check the "Head Blocker".

    There is a column "Head Blocker" under the Processes tab which returns 0 or 1 for a head blocker session.


    Sujeet Singh

  • SQLDBAPerth

    Old Hand

    Points: 393

    Thanks for the feedback all - my first article so it's certainly a learning experience.

    I haven't had a chance to play with 2014 yet - hopefully getting to that in the next few weeks - so I had not yet had the opportunity to test it out in that regard.

    Divine - yes you can garner this sort of information from activity monitor but I wanted to present the reader with an understanding of what the blocking chain is and how it appears under the covers of SQL server. In my experience using the DMV's allows a DBA to better customize their administration experience as well as develops a better understanding of what is happening rather than using a pointy clicky gui interface.

    In any regard to all I definitely appreciate the feedback and hopefully will be able to write another article soon!

  • jkelly95z@yahoo.com

    SSChasing Mays

    Points: 620

    thanks for the script, very handy!

    One request/suggestion... Be mindful of CASE as this doesn't work on case-sensitive instances (only requires minor find/replace)

  • Divine Flame

    SSCoach

    Points: 15941

    plamber (9/19/2013)


    In my experience using the DMV's allows a DBA to better customize their administration experience as well as develops a better understanding of what is happening rather than using a pointy clicky gui interface.

    Yes, exactly. I didn't intend to say that one should not use the scripts. I also always encourage everybody to write the code & using the scripts rather than using GUI as i also don't find any good in GUI. I just pointed out that this information is captured in activity monitor as I thought might be not many people are aware about it yet. It just pointed an alternative, nothing else.


    Sujeet Singh

  • Tim Walker.

    SSCertifiable

    Points: 5173

    Thanks Paul - very useful - 5 stars

    .

  • SQLDCH

    SSChampion

    Points: 11522

    Thanks Paul, great contribution.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • @SQLFRNDZ

    SSCrazy Eights

    Points: 9095

    Good Artcle !

    The same can be achived with some additional results from teh activity monitor script where it has the root blocker .

    You can get the script in this way

    Run teh acivity monitor --> check the current sessions using the DMV and find the spid for the Activity monitor and use dbcc inputbuffer to catch the query what activity monitor is running or using SQL profiler

    Here is the script[/url]

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • asif.nitp

    Newbie

    Points: 9

    Hi Paul,

    Nice article, but we may miss few of the session ids by using "sys.dm_exec_request" like below

    Select Session_ID,Blocking_Session_ID

    From Sys.DM_Exec_Requests

    Where Session_ID > 50

    And Blocking_Session_Id <> 0

    Better to use

    select

    r.command,

    r.plan_handle,

    r.wait_type,

    r.wait_resource,

    r.wait_time,

    r.session_id,

    r.blocking_session_id

    from

    sys.dm_exec_requests r

    inner join

    sys.dm_exec_sessions s

    on

    r.session_id=s.session_id

    where

    s.is_user_process=1

    as from SQL 2005 onward its not guaranteed that only SPID>50 will be the User Process ids.

    reference: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9abd297b-4992-47a1-a6d9-fd3147f19248/sql-server-user-spids-less-than-50

  • Tharg

    SSC Eights!

    Points: 920

    Excellent article and one to add to the toolkit. Many thanks Paul.

Viewing 12 posts - 1 through 12 (of 12 total)

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