Self Blocks

Question

Self Blocks

Sandhya-371593

SSC Eights!

Points: 817
More actions
September 10, 2007 at 9:56 am

#70239

Hi,
In one of our Prod Servers recently I have noticed some of the self blocks happening. At times there are more than 10 blockers could be seen. I have got some details from the article http://support.microsoft.com/default.aspx/kb/906344
But in the article its been told that after the installation of SP4 we can notice the self blocks. But we have installed the SP$ a whila ago and the blocks started appearing only a week back. And when the block happens, the SQL Log shows DBCC TRACEON 3604 with the SPID which is getting blocked. Also I have noticed that the blocks mainly happens on the Production hours.
Below is the Server Details:
Product Version: 8.00.2194, Product Level: SP4 - Enterprise Edition
I am concerned as I have heard that Self blocks may cause deadlocks.
Please help !!!
Thanks and Regards
Sandhya

Viewing 9 posts - 1 through 8 (of 8 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Self blocks are usually due to long I/O wait times. You've obviously been able to capture the self blocking. Run sp_lock the next time you see the self blocking and look at the output. This should show you what the self-blocked resource is waiting on. Again, this is usually an I/O resource.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Sandhya-371593 SSC Eights! Points: 817 More actions · Answer 2

Thanks John for your reply..

The problem is still hanging for me.. Using the dbcc inputbuffer results with the EventInfo sp_prepexec;1.

If the problem is with I/O resource is there anything we can do for this? I am quite new into this field.. Pls excuse if it sounds silly.. But can you please throw some light?

Thanks

Sandhya

John Rowan SSC Guru Points: 56440 More actions · Answer 3

You mentioned that you noticed a bit of the self-blocking during production hours. How did you identify the self-blocking to begin with? Was your production system experiencing slowness that you were investigating or did you just stumble accross it? I would say that if you are having performance slowdowns during these times where you see self-blocking, that you should run SELECT * FROM master..sysprocesses and look at the last wait types of the self blocking processes. This could tell you if the wait type is related to I/O, log file, network, etc.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Sandhya-371593 SSC Eights! Points: 817 More actions · Answer 4

Thanks John..

We have BMC Patrol installed on the servers and through the Patrol alerts only I started noticing the blocks. And most of the time the waittype is LATCH_EX

and at times it is CXPACKET.

Sandhya-371593 SSC Eights! Points: 817 More actions · Answer 5

Can anyone help me with the above mentioned topic?

Many thanks in advance..

Sandhya

noeld SSC Guru Points: 96590 More actions · Answer 6

Blocking is a "normal" thing to happen on a very concurrent database as long as it does not goes out of control and starts giving you timeouts!

On the other hand you should *not* be using TRACE FLAG *3604* !!!!

* Noel

Bob Fazio SSChampion Points: 10794 More actions · Answer 7

Noeld, why do you feel the OP should not be using Trace 3604. I understand that trace as the option to send dbcc output to the console. Am I mistaken, or is that a big problem?

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 8

FYI ...

If you are using trace options for capturing information (usually deadlocks) you will want add startup flags -T1204 & -T3605. These capture deadlock information and send it to the SQL errolog file respectively.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."