BizTalk 2006: Optimising Soap Send Ports and SQL query optimizer

Hi Folks,

Once you have gone live in production with your BizTalk system, you will certainly need to tune the system in order to get a high throughput.

There were two things causing the throughput of our system to be slow.

Limitation of connections by the windows server to SOAP/HTTP servers. Limit is 2
A bug in the SQL query optimizer when doing implicit data type conversions

We were processing around 10 000 messages per hour when the two settings above were incorrect. After making some adjustments we bumped up the processing to 100 000 messages per hour. Each messages does the following:

Imported into the database
Sent to a GEO Coder using SOAP
Sent to XSLT engine using SOAP
Sent to a SQL Algorithm system using SOAP
Send to a CRM system using SQL

In all the steps, a transaction log and custom database is used to keep track of the information and also for a custom human workflow application to edit records that are not well formed.

The following is what was needed to get the throughput higher.

SOAP

Edit the BTSNTSvc.exe.config

Add the following section:

    <system.net>
    <connectionManagement>
        <add address = "*" maxconnection = "50"/>
    </connectionManagement>
    </system.net>

What this does is ensures the server can have more than 2 connections (by default) simultaneously when calling a web service or http service. The setting above should be 12 x (Number of processors). We have a quad core processor, so 48-50 should be a good number for us.

You can read more here: http://msdn.microsoft.com/en-us/library/aa545389.aspx

One way of knowing if this is a solution, is checking the event log in BizTalk. If you see allot of web service timeouts and you have set high timeout settings in your configuration files, proxy class and message context properties in orchestrations, then it must be the machine limiting the service calls or the web service needs some tuning. In our case, the server was limiting the number of calls.

What you will see in the Group Hub Page, is many send ports with this status:

READY TO RUN

Once we made the change in the BTSNTSVc.exe.config and restart the host instances, there SOAP send ports no longer had a ready to run status.

After the change, the timeouts in the event log disappeared and the query page looks much better, with ACTIVE status for all SOAP send port.

SQL

For the SQL side, we had many receive locations calling the same stored procedure, but using different values for the parameters. The parameters are the BATCH SIZE and STAGE.

CREATE PROCEDURE [dbo].[GetWorkflowRecord]
@BatchSize int,
@Stage nvarchar(3) = null
AS
BEGIN

In the actual SQL table the Stage column has a data type of varchar(3). If you look above, the the data type is not the same, it is nvarchar(3), this was causing the SQL optimizer to not successfully calculate how the query should run and caused table scans. TO solve the problem we just changed it to varchar(3), and this would stop the stored procedure from doing an implicit conversion when querying the table, since the @stage is in a where clause:

wfr_wfs_code = (‘SUC’)
AND wfr_Batch is null
AND wfr_stg_code = @Stage

Here is a nice blog about it:

http://statisticsio.com/tabid/36/articleType/CategoryView/categoryId/10/query-optimizer.aspx

CREATE PROCEDURE [dbo].[GetWorkflowRecord]
@BatchSize int,
@Stage varchar(3) = null
AS
BEGIN

Once this change was made, I was even able to change the 8 receive locations to call SQL every 1 second and increase the batch size from 150 to 500-1000.

Conclusion

Sometimes small trivial changes can make a huge different in performance, here, we were able to increase the throughout of the system from 240 000 records per day to 1.4 million records per day, with an average rate of 900-1000 messages per minute where each message was executing 10-12 transactions and calling 5-6 web services. Two factors limited the system, SOAP calls and a stored procedure causing table scans since implicit conversion of data in the procedure could not be optimised and detect the correct non-clustered index to use. Two simple change with major positive results! What more can I ask for before the Easter break.