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.
| 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. |
 |
|
|
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. :( |
|
|
|
|
|
|
Blocking between two unrelated tables?