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:-
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!
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.
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!