Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Blocking between two unrelated tables?

Author  Topic 

GavinS
Starting Member

14 Posts

Posted - 2011-10-11 : 08:57:25
Hi Everyone,

Hoping someone can help me with this.

I have set up a Profiler Trace to monitor blocking on a server and have come across a blocked process report which shows a blocked process and a blocking process with queries that are using tables that are unrelated??!? I checked for any refential integrity between the tables and there is nothing. Yet the blocked process report shows them as blocking.

I've not worked with a great deal of blocking through profiler before so perhaps I am missing something quite simple here.

Anyone got any ideas?

Here is the transcript of the blocked process report, with table names changed from our servers and working tables:-

<blocked-process-report monitorLoop="3198038">
<blocked-process>
<process id="processaa87a8" taskpriority="0" logused="0" waitresource="RID: 5:1:23444731:196" waittime="168578" ownerId="2877574739" transactionname="implicit_transaction" lasttranstarted="2011-10-11T11:06:03.550" XDES="0x45827b10" lockMode="U" schedulerid="2" kpid="12728" status="suspended" spid="102" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2011-10-11T11:06:04.600" lastbatchcompleted="2011-10-11T11:06:04.600" clientapp="serverB" hostname="WEB03" hostpid="20880" loginname="Admin" isolationlevel="read committed (2)" xactid="2877574739" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame line="1" sqlhandle="0x02000000ddb20e1c5e71640c8221b0fbe04ebe2f0e8f5561"/>
<frame line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"/>
</executionStack>
<inputbuf>
delete TableB where Type in (8, 23, 10) </inputbuf>
</process>
</blocked-process>
<blocking-process>
<process status="sleeping" spid="76" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2011-10-11T11:08:53.213" lastbatchcompleted="2011-10-11T11:08:53.213" clientapp="ServerA" hostname="WEB04" hostpid="7680" loginname="Admin" isolationlevel="read committed (2)" xactid="2877528264" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack/>
<inputbuf>
SET FMTONLY ON select * from TableA where 1=2 SET FMTONLY OFF </inputbuf>
</process>
</blocking-process>
</blocked-process-report>

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-11 : 09:07:07
Are there triggers or referential integrity involved?
A delete will have to check any table that references it - which is often a table scan.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

GavinS
Starting Member

14 Posts

Posted - 2011-10-11 : 09:21:22
Exactly what I thought to start with. No referential integrity and no triggers involved. I checked both. :(
Go to Top of Page
   

- Advertisement -