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.
Use BizTalkMsgBoxDbSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL READ COMMITTEDSET DEADLOCK_PRIORITY LOWCREATE 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 IntSELECT @avgsize = AVG(nPartSize) from Parts with (nolock)
USE BiztalkMgmtDBDECLARE @batchsize as INTSELECT @batchsize = batchsize from adm_serviceclass with (nolock) where id = '2'DECLARE @LMSThreshold as INTSELECT @LMSThreshold = LMSThreshold from adm_Group with (nolock)DECLARE @suggestedbatchsize as BIGINTSELECT @suggestedbatchsize = @batchsize*@avgsizeDECLARE @ratio as DECIMAL(15,2)SELECT @ratio = @suggestedbatchsize/@LMSThresholdDECLARE @maxfragmentsize as INTSELECT @maxfragmentsize = 10000000
IF(@LMSThreshold > @suggestedbatchsize)BEGINSET @suggestedbatchsize = @LMSThresholdENDIF(@suggestedbatchsize > @maxfragmentsize)BEGINSET @suggestedbatchsize = @maxfragmentsize ENDINSERT INTO ##PartsInfoSELECT '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, @suggestedbatchsizeSELECT * from ##PartsInfoDROP TABLE ##PartsInfo
Large Message Fragment Size Tuning
Use BizTalkMsgBoxDbSET NOCOUNT ONSET TRANSACTION ISOLATION LEVEL READ COMMITTEDSET DEADLOCK_PRIORITY LOWCREATE 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 INTSELECT @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 BIGINTSELECT @largestpart = MAX(nPartSize) from Parts with (nolock)DECLARE @percentagemulti as DECIMAL(5,4)DECLARE @countparts as DECIMALSELECT @countparts = COUNT(*) from Parts with (nolock)DECLARE @countmultiparts as DECIMALSELECT @countmultiparts = COUNT(*) from Parts with (nolock) where nNumFragments <> 1SELECT @percentagemulti = @countmultiparts/@countparts
USE BiztalkMgmtDBDECLARE @LMSFragmentsize as INTSELECT @LMSFragmentsize = LMSFragmentsize from adm_Group with (nolock)DECLARE @suggestedfragmentsize as BIGINTSELECT @suggestedfragmentsize = @avgpartsize * 1.2DECLARE @maxfragmentsize as INTSELECT @maxfragmentsize = 1000000
IF(@suggestedfragmentsize < @LMSFragmentsize)BEGINSet @suggestedfragmentsize = @LMSFragmentsizeEND
IF(@suggestedfragmentsize > @maxfragmentsize)BEGINSet @suggestedfragmentsize = @maxfragmentsize END
USE BiztalkDTADBDECLARE @avgnumfragmentsTracking_Parts1 as DECIMAL(10,2)SELECT @avgnumfragmentsTracking_Parts1 = avg(cast(nNumFragments as DECIMAL(4,2))) from Tracking_Parts1 with (nolock)INSERT INTO ##PartsInfoSELECT '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 ##PartsInfoDROP 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.
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.
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.