Host Queue Tables
What are physically?
Extracted from the book BizTalk Server 2006 The message engine.
Set of tables within the MessageBoxDb associated per Host. Each time you set up a new BizTalk Host, this tables are going to be created in the MessageBox So, basically, the first time you configure BizTalk server the following set of tables are created for BizTalkServerApplicationHost (by default the first created host):
*note that in the image above the same applies to BizTalkServerIsolatedHost
What is the purpose?
BizTalk use this tables to keep references of all the messages that are “live” in the system . That is: Messages with active subscriptions, suspended messages and awaiting messages associated to each host.
The word references it is really important here since the messages itself are going to be saved in another set of tables ( messageparts, parts and fragments) and basically the host tables are just pointers to the Spool table.
Let see it more graphically:
- BizTalkApplicationQ This is the main queue.Contains the list of messages that are pending delivery to a particular host. Generally, this table should not grow too large (it should contain fewer than 100,000 rows). The number of rows in this table indicates the number of messages waiting to be processed. A large number means you could have a backlog
- BizTalkServerApplicationQ_Suspended where references to suspended messages are placed. This is really important. When a message gets suspended it remains in the messagebox until resume or terminate actions occurs. So, as long as the suspended queue is growing, performance is getting affected. This is something non-experienced BizTalk administrators omit and leave suspended messages grow with no control at all. A suspended message can be due, for example, to parsing errors, serialization errors, failed transmissions, or the inability to find a a subscription.
- BizTalkServerApplicationQ_Scheduled The Scheduled queue contains documents that have been processed by BizTalk Server, and are waiting to be transmitted based on the service window that was specified for the message port that the document passed through. The service window indicates a valid time range for transmitting documents.
- InstanceStateMessageReferences_BizTalkServerApplication The State Queue table holds the list of messages that have been processed by an instance but will be needed later. Currently, only orchestrations and MSMQT use this functionality. When an orchestration uses the State Queue, it is usually because the orchestration performed some operations on a message, persisted the message, and might need the message later. This is normal operation, and you should take this into account when determining correct sizing of the State Queue.
Performance Factors of host queue tables
You now know what host tables are, and because of that and Microsoft best practices, you should separate hosts based on different BizTalk functionalities available:
- sending,
- receiving,
- processing,
- tracking functionality. (and just one)
Ok, nice best practice but Why?
Creating multiple BizTalk hosts provides flexibility when configuring the workload in your BizTalk group and is the primary means of distributing processing across the BizTalk Servers in a BizTalk group. Multiple hosts also allow you to stop one host without affecting other hosts. For example, you may want to stop sending messages to let them queue up in the MessageBox database, while still allowing the inbound receiving of messages to occur. Separating host instances by functionality also provides the following benefits:
- Each host instance has its own set of resources such as memory, handles, and threads in the .NET thread pool.
- Multiple BizTalk hosts will also reduce contention on the MessageBox database host queue tables because each host is assigned its own work queue tables in the MessageBox database.
- Throttling is implemented in BizTalk Server at the host-level. This allows you to set different throttling characteristics for each host.
- Security is implemented at the host-level; each host runs under a discrete Windows identity. This would allow you, for example, to give Host_A access to FileShare_B, while not allowing any of the other hosts to access the file share.
- Monitoring: Just take a look at the following local post “Hidden” monitoring benefit isolating host by functionality
Caution creating host instances
Since each host instance is a windows service consumes their own BizTalk machine resources (memory, CPU, disk utilization…)
Increasing the number of host instances will directly increase the number of SQL Server connections and generate additional load to the SQL Server machine hosting the MessageBoxDatabse.
MaxReceiveInterval
This would not work for BizTalk 2010 and later versions. I will update this post as soon as i can ;-)
BizTalk Server uses a polling mechanism to receive messages from its host queues in the MessageBox. The MaxReceiveInterval value in the adm_ServiceClass table of the BizTalk Management (BizTalkMgmtDb) database is the maximum value in milliseconds that each BizTalk host instance will wait until it polls the MessageBox again. The adm_ServiceClass table contains a record for the following service types:
- XLANG/S – for BizTalk orchestration host instances
- Messaging InProcess – for in-process host instances
- MSMQT – for MSMQT adapter host instances
- Messaging Isolated – for out of process host instances, used by the HTTP, SOAP, and certain WCF receive adapter handlers
By default, this value is set to 500 milliseconds and by reducing it,each Host Instance is going to pull their own queues faster.
Caution with MaxReceiveInterval
Decreasing the value for MaxReceiveInterval may cause excessive CPU utilization on the SQL Server computer that houses the MessageBox database instance.