Blog l AIMS Innovation

BizTalk Feature Pack 1 Analytics vs Tracking - SQL Performance Impact

Written by Marius Storsten | May 11, 2017 9:41:50 PM

This is a follow up article to a post I did a couple of days ago. In the previous article I was simply focusing on the impact on CPU from enabling the BizTalk Feature Pack 1 analytics.  In this post I will go a bit deeper and investigate differences on SQL impact between regular tracking and and FP1 analytics.

Test setup

  • BizTalk 2016 with Feature Pack 1 installed
  • single server with both BizTalk Application Server and SQL server 
  • dedicated tracking host
  • three processing hosts (receive, orchestration, send)
  • single BizTalk application with 1 receiveport,  3 orchestrations and 30 sendports. Messages received are routed based on content and filters on sendports.
  • 27-32 KB flatfile messages, 2 messages per second

Measurements - Full tracking (inc message bodies) vs FP1 analytics

  1. sqlservr process - CPU processor time with tracking
  2. sqlservr process - CPU processor time with FP1 analytics
  3. SQL server - transactions/sec & write transactions/sec on the DTA DB with tracking
  4. SQL server - transactions/sec & write transactions/sec on the DTA DB with FP1 analytics
  5. Disks - %read time & %write time with tracking
  6. Disks - %read time & %write time with FP1 analytics

 

1. sqlservr process - CPU processor time with tracking

 

2. sqlservr process - CPU processor time with FP1 analytics

 

3. SQL server - transactions/sec & write transactions/sec on the DTA DB with tracking

 

4. SQL server - transactions/sec & write transactions/sec on the DTA DB with FP1 analytics

 

5. Disks - %read time & %write time with tracking

 

6. Disks - %read time & %write time with FP1 analytics

 

Summary

This is not a perfect test as a single server box setup is never ideal for isolating issues.

"Still, the results are good enough to conclude that regular tracking no longer is the biggest performance hog ."

I somehow expected full tracking to have a much higher impact on disks and SQL compared to the new FP1 analytics.  After all, Microsoft has not mentioned any specific precautions or that the FP1 analytics will impact your BizTalk environment in any way.

Measurements indicate that the FP1 analytics is gathering more data than regular tracking. The Microsoft description states that operational data is also included, meaning the same data that is available in the BizTalk Group hub. Anyhow, I thought that Microsoft finally had optimized the harvesting of data, which they obviously have not.

As in my previous test, I would not recommend this as an "always-on" feature due to the high performance impact. As with tracking, never enable it in your production environment unless you really need to. But at least tools like Power BI provides a much improved "window" to your BizTalk data when needed.