BizTalk Server Performance Tips: #1 – Enable Text in Row

Happy New Year everyone !

I’ve had for the last 2 years, and still having, a deep dive into Microsoft BizTalk Server especially around performance optimisation. So, I’d like to share some of my experiences and exposures.

My first tip – Enable Text in Row – comes from considering the message size that I was dealing with; the messages were predominately small in size where the most important and time-critical message request and response pair were 1Kb and 3Kb respectively and suit this tip.

By taking advantage of a SQL Server option called “text in row”, we can tell SQL Server that the contents of the fields of type text, ntext, or image data whose dimensions are smaller than those of a data page (8Kb) to store the contents in the data row rather than storing a “pointer” to where that data is actually stored (which means SQL Server needs to perform another read operation to retrieve the actual data!!).

The “text in row” option is detailed in TechNet at http://technet.microsoft.com/en-us/library/ms173530(v=sql.105).aspx. (Though I have to note that Microsoft have been saying that they’ll be deprecating this option since SQL 2005 …).

Here’s the SQL that I used for enabling this option, which sets the maximum size to be stored in the row to 7000 bytes:-

USE [BizTalkMsgBoxDb]

EXEC sp_tableoption N’Spool’, ‘text in row’, ‘7000’

EXEC sp_tableoption N’Parts’, ‘text in row’, ‘7000’

Also, when your orchestrations have a small serialised/persistance footprint, the same tip can be applied to the host’s DynamicStateInfo table. (Of course, this assume you’ve created new and separated your hosts and host instances from those that come out-of-the-box… I think I’ll tackle that one as another tip 🙂 ) . Here’s the SQL that I used for enabling this; please replace OrchestrationHost with the real name for your host!

USE [BizTalkMsgBoxDb]

EXEC sp_tableoption N’DynamicStateInfo_OrchestrationHost‘, ‘text in row’, ‘7000’

Furthermore, if you wish to ‘undo’ this tip, then the following SQL will be handy.

USE [BizTalkMsgBoxDb]

EXEC sp_tableoption N’Spool’, ‘text in row’, ‘OFF’

EXEC sp_tableoption N’Parts’, ‘text in row’, ‘OFF’

EXEC sp_tableoption N’DynamicStateInfo_OrchestrationHost‘, ‘text in row’, ‘OFF’

Until next tip… be good!

Advertisements

2 comments

  1. Hi John, this is an interesting performance tip. I’m usually quite wary of making any changes to the out of the box BizTalk databases. Have you actually got this setting running in a live and busy environment? Has it been running without any issues?

    Cheers
    Johann

    1. Johann,

      Yes, I actually have this running on a live environment. The application is ‘sporadic’ in the sense that its key use – at the moment, but there’s more coming – is around stockcount activities on a regular basis. On those days, the use is very heavy.

      But remember, this tip saves the double read in SQL Server and the benefits come when message sizes are small enough.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s