Blocking Chain (SQL Spackle)

Question

Post reply

Blocking Chain (SQL Spackle)

SQLDBAPerth

Old Hand

Points: 393
More actions
September 18, 2013 at 11:57 pm

#277316

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

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

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

i_moura71 Valued Member Points: 65 More actions · Answer 1

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 More actions · Answer 2

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: 15943 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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: 15943 More actions · Answer 6

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 More actions · Answer 7

Tim Walker.

SSCertifiable

Points: 5173

September 19, 2013 at 6:49 am

#1651487

Thanks Paul - very useful - 5 stars

.

SQLDCH SSChampion Points: 11601 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

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: 980 More actions · Answer 11

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