Monitoring BizTalk Performance Counters for throttling

In occasions when a BizTalk server has a big load to process often we will notice every thing slows down and it seems BizTalk is not processing any more messages.

This happens when a throttling state was achieved, and BizTalk will stop processing messages, In my case the Message delivery throttling state had the value of 3 meaning “Throttling due to high in-process message count” we had too many messages going in, allowing the host to use more memory(see below) solved the problem for the amount of messages that where input to BizTalk.

To monitor BizTalk throttling states we can use some performance counters, to add the counters:

go to:  Performance monitor -> BizTalk:MessageAgent

The description for each of the performance counters can be found here msdn.

Tip: on 64 bit machines we can allow the host to use up to 50% of the memory on the server that will allow BizTalk the ability to have a higher throughput.

On the host configuration click advanced -> throttling trash hold -> Process memory usage (on 64 bit can increase to 50%)


Using the Tuning Advisor to to improve SQL performance

This post is about how to use the DataBase Engine Tuning Advisor.

Performance is a key issue in solutions and performance has very much to do with calling SQL procedures and avoiding deadlocks so tuning up SP can significantly boost performance.

SQL provides the DataBase Engine Tuning Advisor which is a tool to help indexing SQL tables.

In order to evaluate the DB performance we need to have a sample to tune on, this is where the SQL Sever profiler comes in.

How to create a Trace file:

1. On the SQL management studio go to tools -> SQL Profiler.

2. Start a new trace make sure you specify a file to save to and use the Tuning template


3. Select the Events Selection tab and click on Column Filters to bring up the Edit Filter window, now specify the name of the DB to trace


4. Press run to start tracing, now is the point where you need to start using your application, in my case pushing messages in to the BizTalk server and the more the better try to cover as much scenarios as possible especially scenarios that interact with SQL Server so the trace can have good statistics.


5. After letting the systems run for some time (In my case I let the system run for a couple of minutes)  we can stop the trace and now its time to use the tuning advisor, From the SQL management studio/or the profiler itself go to tools -> DataBase Engine Tuning Advisor.

6. Create a new session specify the trace file created in the Profiler, select the workload database which is you DB and select the same DB to tune(I selected all tables) then press the Start Analysis button. should look like the following


7. When the analysis is finished we get a recommendation of which tables to index and actually the script itself, notice the estimated improvement percentage on the top left and bellow are some other screen shots of the report that was generated


The reports, other reports are available in the select report drop down


After creating the indexes and running the tuning advisor again I managed to get 0% estimated improvement, Note before creating the indexes one of the SP had occasional Dead Locks, after indexing that never happened again.

A good reason to use the tuning Advisor is to sort out Dead Lock errors (in my case I over indexed several columns which can actually decrease performance).