Should the WCF-SQL adapter be used in low latency scenarios?

Posted: March 22, 2011  |  Categories: BizTalk SQL

I recently outlined a low latency solution using BizTalk 2010 where the response had to return in less than 1 second 99% of the time.

The solution was tested in UAT to prove that the above requirement was meet. Once the solution was deployed to production we were surprised to find that we did not meet this requirement.

In production we observed the following request response latency using performance monitor.latencywithgaps

The first request response is high as we expect but then after and subsequent requests in quick succession are low less than 0.5 of a second but then after an interval of a minute the latency increases to over a second again.

After examining the Orchestration Debugger for ones that take longer than a second we can see that the WCF-SQL request response send  takes more than 0.5 second. If my WCF SQL send adapter is used frequently the request/response completes in less than 0.2 seconds. If no message goes through the send port for about 1-2 minutes then the next request/response cycle takes a lot longer to complete . It is typically about 0.9 second.

Who cares about 0.7 second you say? Well I do because my entire low latency application has to complete in less than 1 second 99% of the time. I can achieve that if the WCF SQL adapter did not show the behaviour described above.

I have used SQL profiler to trace what is happening on the target SQL server and it shows that on SQL server the transaction always completes in less than 10 milliseconds.The SQL adapter is currently set to the standard binding defaults that you use for a SQL adapter.

Checking the SQL trace we can see that the SP called executes in a less than 10ms but also we notice if the BizTalk SQL server connection is inactive for approximately 1 minute it is closed.

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top