------------------------------------- 
Addendum – June 22 2007: Under absolutely NO circumstances should you modify the data model of the BizTalk databases in any way.  You should only optimize your own application databases.
------------------------------------- 

I have seen this issue in many places,  which still surprises me because there are a lot of blog entries about this.  For what it's worth, here's one more.

Scenario: BizTalk solution makes moderate to heavy use of SQL adapter in calling stored procedures in a database.

Problem: SQL server shows many blocking (perhaps deadlocking) SPIDs, most of which are owned by the BizTalk host instance account.

Factors:

  1. The longer the procedure takes to execute, the worse off you are
    1. Big tables, poor logic, bad indexes, bad db maintenance, disk contention etc.
  2. The more concurrent procedure executions the worse off you are
    1. short polling interval
    2. multiple ports
    3. multiple host instances

Diagnosing:

  1. Enterprise Manager or Management Studio can give some insight to active and blocking spids (check to see who owns the blocking spid and use dbcc inputbuffer to see the sql it is executing) 
  2. Try using sp_who,sp_who2,and sp_who3, they may give you lots of insight
  3. Call MS PSS (or download pssdiag for yourself from the MS download site) and have them configure a pssdiag trace for you.  Look especially closely at the blocker output and the profiler trace.  Use rml.exe (read80trace) to generate a report of the "worst offenders" sql scripts or procedures.

Fixes:

  1. As per documentation, don't use T-SQL transactions in your proc.  The SQL Adapter already has this all wrapped up in a distributed transaction so relax!
  2. As per documentation, you can try setting the transaction isolation level to READ COMMITTED, or perhaps REPEATABLE READ.  If you are running SERIALIZABLE that is a great way to tank performance and cause blocking.
  3. Speed up the procs (Archive, purge, index, create views, run db maintenance, buy a big SAN, whatever. The locks won't kill you if they don't last) 
  4. Don't go silly with coarse locking hints.  Keep your lock few in number and small in granuarity.  If you must manually lock, then please read up on the READPAST hint - especially for queue or "trigger" tables. (Thanks to Kunjal K for this hint)
  5. Reduce concurrency - You can try lengthening your polling interval or reducing the number of host instances that run that port.

Didn't find what you were looking for? Try one of these:

  1. Known SQL Adapter Issues
  2. How to resolve a deadlock
  3. Understanding Isolation Levels
  4. Contact me through comment below

Is there something that I can do to make this blog more helpful to you?
I welcome your feedback!