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.

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

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.