Recently one of our customer experienced performance issues with BizTalk, where transaction queues were filling up and it was taking too much time to process normal transaction activity. So I went onsite, checked the obvious, found two familiar performance related issues...
Issue 1:Verfied built in SQL jobs history, found that DTA Purge and Archive (BizTalkDTADb) has not run on the production BizTalk server since September 2007. BizTalk tracking data file was about 1.6GB, obviously, which results in performance degradation. Analysis: DTA Purge and Archive SQL job is expected to run every day (24 X 7) about 15-20 minutes apart. As time goes, the BizTalkDTADb database will grow in size and unchecked growth will result in poor performance. Possibly having an adverse impact on message box database performance as well...
Resolution: Followed the steps detailed in this MSDN article:
http://technet.microsoft.com/en-us/library/aa560754.aspx
exec dtasp_BackupAndPurgeTrackingDatabase
0, --@nLiveHours,
30, --@nLiveDays
35, --@nHardDeleteDays
‘\\D:\SQL backup’, --@nvcFolder
null, --@nvcValidatingServer
0 --@fForceBackup Soft purge process
After performing the steps above (once the tracking data is purged) "DTA Purge and Archive (BizTalkDTADb)", set the SQL Job is enabled and is running at every 20 minutes. Database size came down to 30 MB from 1.6 GB!
To Understand DTA Tracking performance behavior, refer to these MSDN article:
http://msdn.microsoft.com/en-us/library/cc296718.aspx
Issue 2: There were three BizTalk hosts in the production environment including the default BizTalk host instance and other two being txHost1 and txHost2. All the three host instances had tracking turned on.
Analysis: According to BizTalk documentation, "Allow host tracking" check box must be enabled on a single host instance and ideally on the default in-process BizTalk Application Host. "Allow host tracking" should not be enabled on any other Host instances.
Resolution: Removed tracking from txHost1 and txHost2 hosts, kept it unchanged on the BizTalk Application Host and restarted all the three hosts.
Creayed and configured three new host instances such as orchHost for all orchestrations, rcvHost for all receive locations and sndHost for all send ports. Tested on the QA server with 1577 transactions, it took about 17 minutes to process all transactions, thus processed about 100 transactions a minute, improving the performance significantly...
For more info on tracking host: http://msdn.microsoft.com/en-us/library/cc296878.aspx
