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
| Title | Avg Part Size | Batch Size of Messaging In Process | Actual Large Message Threshold Setting from Group | Suggested Large Message Threshold Size |
| 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 | 110 | 20 | 1000000 | 1000000 |
likewise, the query for Large Message Fragment Size Tuning shows no suggested increase.
+
MsgBox Db : Large Message Fragment Size Tuning
| Title | Avg Part Size | Avg Num Fragments | Current Large Message Fragment Size | Suggested Large Message Fragment Size | # Rows in Parts Table | Largest Part | % Rows with Multiple Fragments | Historical Avg Num Fragments DTA |
| 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. | 102 | 1.00 | 102400 | 102400 | 14706 | 152 | 0 | 1.00 |
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
| Title | Avg Part Size | Avg Num Fragments | Current Large Message Fragment Size | Suggested Large Message Fragment Size | # Rows in Parts Table | Largest Part | % Rows with Multiple Fragments | Historical Avg Num Fragments DTA |
| 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. | 123755 | 1.93 | 102400 | 148506 | 4797 | 2648297 | 5 | 1.04 |
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
| Title | Avg Part Size | Avg Num Fragments | Current Large Message Fragment Size | Suggested Large Message Fragment Size | # Rows in Parts Table | Largest Part | % Rows with Multiple Fragments | Historical Avg Num Fragments DTA |
| 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. | 2648297 | 21.00 | 102400 | 1000000 | 500 | 2648297 | 100 | 1.27 |
+
MsgBox Db : LargeMessageThreshold Information
| Title | Avg Part Size | Batch Size of Messaging In Process | Actual Large Message Threshold Setting from Group | Suggested Large Message Threshold Size |
| 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 | 2648297 | 20 | 1000000 | 10000000 |
I put some warnings into the MBV tool which will be published soon that recommend looking at tuning.
-
Performance
| Item Caption | Item Value | Query Report | URLs |
| The Average part size is > the Current Large Message Fragment Size. You can improve performance by increasing the large message fragment size See the MsgBox Db : Parts Info Section for more information | The avg # of fragments is : 21.00 for best performance tune Large message fragment size upward until the avg # of fragments is 1! | <> | How BizTalk Server Processes Large Messages |
| Tune Large Message Threshold | See LargeMessageThreshold Information section. 10000000 can be used as a guideline for tuning the Large Message Threshold for best performance! | <> | How BizTalk Server Processes Large Messages |
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.