Issues causing high CPU usage on BizTalk SQL Server

I recently got into a situation where customer reported that the CPU usage on the BizTalk SQL Server in production environment is shooting to 100% and it’s impacting the processing.

Customer had also enabled the performance counters on BizTalk server and from their perfmon log they came to know that there is Message Publishing throttling state = 6

I started looking a bit deeper into the BizTalkMsgBoxDb with the help of MsgBoxViewer and found that the tracking data from the trackingdata_0_X tables was not being moved to the BAMPrimaryImport. I then checked to make sure that the tracking host was running and it was, it then became a bit more puzzling, the trackingdata_1_X tables were being move to the DTA database, so part of the tracking was working.

TrackingData_0_x: These four tables store the Business Activity Monitoring (BAM) tracked events in the BizTalkMsgBoxDb database for TDDS to move the events to the BAMPrimaryImport database.

TrackingData_1_x: These four tables store the tracked events in the BizTalkMsgBoxDb database for TDDS to move the events to the BizTalkDTADb database.

So basically both the MessageBox database and Tracking database have these sets of table with same structure.

So when you first start the tracking host, it looks at the sequence number in the TDDS_StreamStatus table of DTA database.

Let’s say for example the sequence number is 10039.

So now the TDDS will look for the sequence number greater than 10039 in the MessageBox tracking tables and if gets it will move the data from MessageBox tracking table to DTA or BAM tracking tables. So that’s the way it maintains the order.

But there are some issue here. For example let’s say if you change the sequence number to less than 10039 i.e. 10038 in MessageBox tracking table, then TDDS will never see this row because it always look for the sequence number greater than the sequence number in the TDDS_StreamStatus table of DTA.

There are couple of common reasons for the mismatch sequence numbers

• If you run the clean up script(Msgbox_cleanup_logic.sql) on MessageBox database. This cleanup will truncate the tracking tables in MessageBox Database and hence the sequence number will go out of sequence.
• When there is a burst of heavy load that might create a huge backlog of tracking data in the MsgBox tables.Consequently, TDDS and the jobs will struggle to catch up with the back logs and that can cause the CPU spike.

Once you have the mismatch of sequence number, then data will not move from MessageBox and hence its size will start increasing which can cause database throttling.

Also the query in the sp [dbo].[MsgBoxPerfCounters_GetTrackingDataSize] runs in a continuous loop and takes the high CPU cycles because of large records in TrackingData table.

So when I looked at the current sequence number in the table TDDS_StreamStatus they were much smaller than the Sequence Numbers in the trackingdata_0_X table in the BizTalkMsgBoxDb. So this was the case where a burst of heavy load was put on the system that might have created a huge backlog of Tracking data in the MessageBox tables.

Consequently, TDDS and the jobs were struggling to catch up with the back logs and that should have caused the CPU spike.

After some hours of slow processing the difference in the sequence number went down and hence the CPU usage.

Instead of waiting, I could have tried mitigation strategies, increasing the Message count in DB or multiplier value for Tracking data to stop the Publishing throttling state.

Also in this scenario one should look into the TDDS_FailedTrackingData table where error gets populated whenever there is a tracking failure.

Related Links

http://www.biztalkbill.com/Home/tabid/40/EntryId/91/Issues-with-Tracking-causing-high-CPU-usage-on-BizTalk-SQL-Server.aspx

http://shaileshbiztalk.blogspot.in/2011/12/dealing-with-biztalk-high-cpu-issues-on.html

http://blogs.biztalk360.com/biztalk-environment-maintenance-from-a-dba-perspective/

http://blogs.msdn.com/b/biztalknotes/archive/2013/11/26/bam-tracking-data-getting-accumulated-in-the-biztalkmsgbox-database.aspx