Tuning Large Message Threshold and Fragment Size

Seems like not many Biztalk installations are tuning the Large Message Threshold and Fragment size.  And the problem is that not many Biztalk admins can tell you off the top of their head how large the average message size is and how large the largest messages coming through their system are.  I had a case the other day which caused me to think more about this issue because the environment was experiencing DTC timeouts for certain operations.    The Large Message Threshold was at the default for Biztalk 2004 which is 102400, but this was a Biztalk 2006 installation that had been upgraded from 2004.   After changing the Large Message Threshold to the default value, the problem stopped occurring.   Incidentally, I think that the default value of 102400 for the Large Message Threshold on Biztalk 2004 may even be too low for Biztalk 2004.

So I wrote some queries to get the information about the configuration and return data from the parts table and information about batch size.

LargeMessageThreshold Information

Use BizTalkMsgBoxDb
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW
CREATE TABLE ##PartsInfo (Title nvarchar(512), [Avg Part Size] FLOAT NULL,[Batch Size of Messaging In Process] BIGINT NULL, [Actual Large Message Threshold Setting from Group] BIGInt NULL, [Suggested Large Message Threshold Size] BIGInt NULL)
DECLARE @avgsize as Int
SELECT  @avgsize = AVG(nPartSize) from Parts with (nolock)

USE BiztalkMgmtDB
DECLARE @batchsize as INT
SELECT  @batchsize = batchsize from adm_serviceclass with (nolock) where id = '2'
DECLARE @LMSThreshold as INT
SELECT  @LMSThreshold = LMSThreshold from adm_Group with (nolock)
DECLARE @suggestedbatchsize as BIGINT
SELECT  @suggestedbatchsize = @batchsize*@avgsize
DECLARE @ratio  as DECIMAL(15,2)
SELECT @ratio = @suggestedbatchsize/@LMSThreshold
DECLARE @maxfragmentsize as INT
SELECT @maxfragmentsize = 10000000

IF(@LMSThreshold > @suggestedbatchsize)
BEGIN
SET @suggestedbatchsize = @LMSThreshold
END
IF(@suggestedbatchsize > @maxfragmentsize)
BEGIN
SET @suggestedbatchsize = @maxfragmentsize
END
INSERT INTO ##PartsInfo
SELECT  'For Best performance ensure that the Large message threshold is greater than the  batch size * the average Message size.  Use the Suggested Large Message Threshold Size as a guideline in order to decrease the # of distributed transactions.  Note:  Increasing this value may increase the amount of memory used by sql server, especially when using very large batch sizes like 100',@avgsize, @batchsize, @LMSThreshold, @suggestedbatchsize
SELECT * from  ##PartsInfo
DROP TABLE ##PartsInfo

Large Message Fragment Size Tuning

Use BizTalkMsgBoxDb
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW
CREATE TABLE ##PartsInfo (Title nvarchar(512), [Avg Part Size] FLOAT NULL,[Avg Num Fragments] DECIMAL(10,2) NULL, [Current Large Message Fragment Size] Int NULL, [Suggested Large Message Fragment Size] Int NULL, [# Rows in Parts Table] BIGINT NULL, [Largest Part] BIGINT NULL, [% Rows with Multiple Fragments] DECIMAL NULL, [Historical Avg Num Fragments DTA] DECIMAL(10,2) NULL )
DECLARE @avgpartsize as INT
SELECT  @avgpartsize = AVG(nPartSize) from Parts with (nolock)
DECLARE @avgnumfragments  as DECIMAL(10,2)
SELECT @avgnumfragments = avg(cast(nNumFragments as DECIMAL(10,2))) from Parts with (nolock)
DECLARE @largestpart as BIGINT
SELECT @largestpart = MAX(nPartSize) from Parts with (nolock)
DECLARE @percentagemulti as DECIMAL(5,4)
DECLARE @countparts as DECIMAL
SELECT  @countparts = COUNT(*) from Parts with (nolock)
DECLARE @countmultiparts as DECIMAL
SELECT  @countmultiparts = COUNT(*) from Parts with (nolock) where nNumFragments <> 1
SELECT  @percentagemulti = @countmultiparts/@countparts

USE BiztalkMgmtDB
DECLARE @LMSFragmentsize as INT
SELECT  @LMSFragmentsize = LMSFragmentsize from adm_Group with (nolock)
DECLARE @suggestedfragmentsize as BIGINT
SELECT  @suggestedfragmentsize = @avgpartsize * 1.2
DECLARE @maxfragmentsize as INT
SELECT @maxfragmentsize = 1000000

IF(@suggestedfragmentsize < @LMSFragmentsize)
BEGIN
Set @suggestedfragmentsize = @LMSFragmentsize
END

IF(@suggestedfragmentsize > @maxfragmentsize)
BEGIN
Set @suggestedfragmentsize = @maxfragmentsize
END

USE BiztalkDTADB
DECLARE @avgnumfragmentsTracking_Parts1 as DECIMAL(10,2)
SELECT  @avgnumfragmentsTracking_Parts1 =  avg(cast(nNumFragments as DECIMAL(4,2))) from Tracking_Parts1 with (nolock)
INSERT INTO ##PartsInfo
SELECT  'For Best performance ensure that the Large message fragment size is set  to a value greater than the Avg Part Size so that Avg Number of Fragments is close to 1.  Use the Suggested fragment size as a guideline in order to decrease the # of fragments.  Note increasing this value may increase the amount of memory that is used in a host instance. ',@avgpartsize, @avgnumfragments, @LMSFragmentsize, @suggestedfragmentsize, @countparts, @largestpart, (100.00)* @percentagemulti, @avgnumfragmentsTracking_Parts1

SELECT * from  ##PartsInfo
DROP TABLE ##PartsInfo

In a standard scenario with very small message sizes, you might see something like this which basically shows no suggested change in Large Message Threshold size.

+

MsgBox Db : LargeMessageThreshold Information

likewise, the query for Large Message Fragment Size Tuning shows no suggested increase.

+

MsgBox Db : Large Message Fragment Size Tuning

But then as I increase the size of the messages by dropping 500 2.5 mb files, the average part size begins to increase.  Here it is 123,755, the Avg Number of Fragments is 1.93, the current large message fragment size is 102,400 which is the default, the Suggested Large Message Fragment Size is the Avg part Size * 1.20.  4797 rows are in the parts table.   The Largest Part is 2648297 bytes and only 5 % of the rows currently in the parts table have Multiple Fragments this is because I dropped 10,000 1 kb files and 500 2.5 mb files for the test.   The Historical Average is taken from the DTA database.  If Message Body Tracking is enabled I read this from the Tracking_Parts1 table.  If the value is NULL, then Message Body Tracking is not enabled.

+

MsgBox Db : Large Message Fragment Size Tuning

As the Avg Part Size increases, so does the Suggested Large Message Threshold Size. The maximum is 10 million bytes, this recommends 2.5 mb or so. A couple of issues, the calculation doesn't account for multipart messages. So the message could actually be much larger depending on the # of parts. Also the Batch size is read from Messaging In Process. Individual adapters may specify a batch size of much larger than 20, in which case many more transactions will be created. I saw one 3rd party adapter the other day with a batch size of 100. In this case the Suggested Large Message Threshold would be higher.

As the avg Part Size gets very large, I max out the recommendations. I don't think that there will be many environments that hit the max, and it would be prudent to make sure that you do performance testing before maxing these values out. See the Notes below for more information.

+

MsgBox Db : Large Message Fragment Size Tuning

+

MsgBox Db : LargeMessageThreshold Information

I put some warnings into the MBV tool which will be published soon that recommend looking at tuning.

-

Performance

I have seen positive results by using this data to help determine what an optimal fragment and threshold size is with the goal of reducing the # of Distributed transactions and round trips.