BizTalk Server - Performance Tuning

While considering performance improvement of a BizTalk Server or a BizTalk application, steps needs to be taken while writing your code as well as while setting up the BizTalk Server Environment along with the application deployment. Below are some points one should consider while setting up the BizTalk Environment.

1. Verify that BizTalk SQL Server Agent Jobs are up & running

BizTalk Server includes several SQL Server Agent jobs that perform important functions to keep your servers operational and healthy. You should monitor the health of these jobs and ensure they are running without errors. One of the most common causes of performance problems in BizTalk Server is the BizTalk Server SQL Agent Jobs are not running, which in turn can cause the MessageBox and Tracking databases to grow unchecked. One should verify that the BizTalk Server SQL Server Agent jobs are completing in a timely manner without any errors.

2. Define auto-growth settings for BizTalk Server databases to a fixed value instead of a percentage value

Database auto-growth should be set to a fixed number of megabytes instead of to a percentage (specify file growth In Megabytes). This should be done so that, if auto-growth occurs, it does so in a measured fashion. This reduces the likelihood of excessive database growth. The growth increment for BizTalk Server databases should generally be no lower than 100 MB.

3. Even Distribution of Host & Host Instances 

On the production environment, one should take every necessary step to distribute the hosts & respective host instance across multiple servers available in the BizTalk Server Farm in such a manner that one host should not be highly loaded and load should be equally distributed accordingly in order to achieve good performance of the application & BizTalk server.

4. Complete & Proper Deployment on Production Server 

In case of multiple server BizTalk Farm, you should take care that every application should be completely deployed on BizTalk and properly installed on all the servers in the farm along with all required settings (BizTalk Config file, registry, SSO, any xml configuration, etc.)

5. Move the Backup BizTalk Server output directory to a dedicated SAN/NAS 

Move the Backup BizTalk (Full and Log backup) output directory to a dedicated SAN/NAS, and then edit the Backup BizTalk Server job to point to the dedicated SAN/NAS. Moving the Backup BizTalk Server output directory to a dedicated SAN/NAS will reduce disk I/O contention when the job is running by writing to a different disk than the job is reading from.

6. SQL Server Max Degree Of Parallelism (MDOP)

 The Parallelism setting governs the number of processors on which any particular Transact-SQL statement can run at the same time. If the Parallelism setting is set to use one processor, the SQL Server query optimizer will not create execution plans that permit any particular Transact-SQL statement to run on multiple processors at the same time. Max Degree of Parallelism is set to “1” during the configuration of BizTalk Server for the SQL Server instance(s) that host the BizTalk Server MessageBox database(s). This is a SQL Server instance-level setting. This setting should not be changed from this value of “1”. Changing this to anything other than 1 can have a significant negative impact on the BizTalk Server stored procedures and performance.

                                                                     

7. SQL Server Statistics on the Message Box Database 

Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. The following options are turned off by default in the BizTalk Server MessageBox database when it is created:

a.       Auto Create Statistics

b.      Auto Update Statistics

Do not enable these options on MessageBox databases. Enabling the "auto create statistics" and "auto update statistics" options can cause undesirable query execution delays, especially in a high-load environment.

8. Setting the “text in a row” table option on specific MessageBox database tables 

SQL Server provides a table option called text in row to declare that the contents of the fields of type text, ntext, or image data whose dimensions are smaller than those of a data page (8Kb) must be stored in a data row. By setting this option on BizTalkMsgBoxDb tables (Parts table and Spool table), you can increase message throughput when working with small messages which have a small context and orchestrations that have a small persistence size.