How to exploit the Text In Row table option to boost BizTalk Server Performance

  • Article

 

Text in Row Table Option in SQL Server

In SQL Server, text, ntext, or image fields are large character or binary strings (up to 2 gigabytes) that by default stored outside a data row. The data row contains only a 16-byte text pointer that points to the root node of a tree built of internal pointers. These pointers map the pages in which the string fragments are stored.

SQL Server provides a table option called text in row to declare that the contents of the fields of type text, ntext, or image whose dimensions are smaller than those of a data page (8Kb) must be stored in the data row. When a large value type or a large object data type column value is stored in the data row, the Database Engine does not have to access a separate page or set of pages to read or write the character or binary string. This makes reading and writing the in-row strings about as fast as reading or writing limited size varchar, nvarchar, or varbinary strings. Similarly, when the values are stored off-row, the Database Engine incurs an additional page read or write.

For more information on this topic, see the following articles:

SQL Server 2005

SQL Server 2008

How to exploit the Text in Row table option in BizTalk Server

The following section explains how and when applying the text in row table option to boost BizTalk performance.

Parts Table

When the message size is less than 8k the text in row table option can be applied to the Parts table that has 2 fields of type image:

  • imgPart: contains a message Part or message part fragment
  • imgPropBag: contains the message part property bag.

This technique can lead to a decrease of CPU usage up to 20-30% on the SQL and BizTalk nodes and to a significant improvement in terms of latency and throughput, when messages are smaller than 8kb.

Spool Table

Similarly, when the average size of the message context is less than 8 kb, this technique can be extended to the Spool table. This latter contains a column of type image:

  • imgContext: this field contains the message context.

To successfully apply this technique to the Spool table, you must eliminate unnecessary context properties and distinguished fields to reduce the size of the message context below 8 Kb.

DynamicStateInfo Tables

Finally, the text in row table option can be applied to the DynamicStateInfo_Host tables that contain the serialized state of orchestrations when they encounter a persistence point during their execution. These tables, one for each host, contain a field of type image:

  • imgData: contains binary-serialized orchestrations

When orchestrations running within a host HostA are so small that their size once serialized is less than 8 kb, the text in row technique can successfully be applied to the DynamicStateInfo_HostA table. Therefore it’s advisable to keep the internal state of orchestrations as small as possible. This technique can significantly reduce the time spent by the XLANG Engine to serialize, persist and restore the internal state of an orchestration in case of persistence point. Moreover, when the size of this latter is smaller than 8 Kb, it allows using the text in row technique with DynamicStateInfo_Host tables. The following techniques can be used to reduce the memory footprint of orchestrations:

· Create variables and messages as late as possible and release them as early as possible for example introducing non transactional scopes inside your orchestrations and declaring variables and messages within these inner scopes instead of declaring them at the top-most level.

· Use static variables and components whenever possible. When a persistence point occurs, these latter will not be serialized along with the orchestration.

Text In Row in Action

During a recent Performance and Stability review with a customer, I applied the text in row technique to the Parts table (see the picture below) of the BizTalkMsgBoxDb as the average size of messages processed by the BizTalk application was less than 2 Kb.

 EXEC sp_tableoption N'Parts', 'text in row', '4000'

This led to a decrease of 22% in % Processor Time on BizTalk nodes and to a significant reduction of I/O contention on the SQL Server instance hosting the BizTalkMsgBoxDb.

References

For more information on this topic, see the following articles:

  • “What you can and can't do with the MessageBox Database server” topic in the BizTalk Core Engine's Weblog .
  • "BizTalk Server Database Optimization" article on MSDN.