Excessive Blocking when using BizTalk SQL Adapter

by | Jun 19, 2007 | BizTalk Community Blogs via Syndication


————————————- 
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!

Initial Setup

by | Jun 19, 2007 | BizTalk Community Blogs via Syndication

This will be the first of many blog postings on this new MSDN blog site.  I’ll start off with a bunch of BizTalk, SQL, and .NET stuff.  I have no idea where it will go from there, but I’ll try to practice all of my lessons learned from my previous blog and keep the community happy as much as is possible. (I recommend reading the Automatic Account Creation post for BizTalk.  By far, it was my most popular entry.


 Thanks for stopping by, and please, send requests if you have them.

Of empty elements & unexpected whitespace…

by | Jun 18, 2007 | BizTalk Community Blogs via Syndication

A flat file schema can be configured to generate empty elements for empty content
– using the “Generate Empty Nodes” option.

It seems that at runtime, the flat file dissasembler can generate “<foo></foo>”
for these empty nodes in cases where “Validate Instance…”
in the IDE would have generated “<foo/>”.

Where this can cause pain is if you receive a message like: “<foo><bar></bar></foo>”

into your orchestration, and then do a series of assignments like: 

xmlDocVariable = myMessage;
// xmlDocVariable2 initialized because xmlDocVariable is needed for other things...
xmlDocVariable2.LoadXml(xmlDocVariable.OuterXml);
myMessage2 = xmlDocVariable2;

At this point, myMessage2 is going to be the serialized representation of xmlDocVariable2. 
By default, you will now get: 

<foo>
..<bar>
..</bar>
</foo>

Notice you now have carriage returns, line feeds, and spaces (shown here as periods)
involved.  Depending on how you now go after /foo/bar, this can be bad (i.e.
you won’t get empty content when you might expect it.)  You can avoid this
behavior by doing: 

xmlDocVariable2.LoadXml(xmlDocVariable.OuterXml);
xmlDocVariable2.PreserveWhitespace = true;
myMessage2 = xmlDocVariable2;

Thanks to Tomas and Carlos for
setting me straight on PreserveWhitespace – I was trying to set it before the
call to LoadXml, and this doesn’t work.  Never a dull day in BizTalk land…

The documentation
for PreserveWhitespace is a little strange.  This functionality would seem
to be better represented as parameters to Load/Save, rather than as a property…

“If PreserveWhitespace is true before Load or LoadXml is called, white
space nodes are preserved; otherwise, if this property is false, significant white
space is preserved, white space is not.  If PreserveWhitespace is true before
Save is called, white space in the document is preserved in the output; otherwise,
if this property is false, XmlDocument auto-indents the output.”

Of empty elements & unexpected whitespace…

by | Jun 18, 2007 | BizTalk Community Blogs via Syndication

A flat file schema can be configured to generate empty elements for empty content
– using the “Generate Empty Nodes” option.

It seems that at runtime, the flat file dissasembler can generate “<foo></foo>”
for these empty nodes in cases where “Validate Instance…”
in the IDE would have generated “<foo/>”.

Where this can cause pain is if you receive a message like: “<foo><bar></bar></foo>”

into your orchestration, and then do a series of assignments like: 

xmlDocVariable = myMessage;
// xmlDocVariable2 initialized because xmlDocVariable is needed for other things...
xmlDocVariable2.LoadXml(xmlDocVariable.OuterXml);
myMessage2 = xmlDocVariable2;

At this point, myMessage2 is going to be the serialized representation of xmlDocVariable2. 
By default, you will now get: 

<foo>
..<bar>
..</bar>
</foo>

Notice you now have carriage returns, line feeds, and spaces (shown here as periods)
involved.  Depending on how you now go after /foo/bar, this can be bad (i.e.
you won’t get empty content when you might expect it.)  You can avoid this
behavior by doing: 

xmlDocVariable2.LoadXml(xmlDocVariable.OuterXml);
xmlDocVariable2.PreserveWhitespace = true;
myMessage2 = xmlDocVariable2;

Thanks to Tomas and Carlos for
setting me straight on PreserveWhitespace – I was trying to set it before the
call to LoadXml, and this doesn’t work.  Never a dull day in BizTalk land…

The documentation
for PreserveWhitespace is a little strange.  This functionality would seem
to be better represented as parameters to Load/Save, rather than as a property…

“If PreserveWhitespace is true before Load or LoadXml is called, white
space nodes are preserved; otherwise, if this property is false, significant white
space is preserved, white space is not.  If PreserveWhitespace is true before
Save is called, white space in the document is preserved in the output; otherwise,
if this property is false, XmlDocument auto-indents the output.”