Share this

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

Loved this? Spread the word


Gautam

Follow me here

About the Author

My name is Gyanendra Kumar Gautam. I am Solution Consultant, who basically works to hook the stuff together using Microsoft technologies like Azure PaaS, Azure Serverless Services, Microsoft BizTalk Server, and Azure DevOps Services.

You may also like

The source was not found, but some or all event logs could not be searched. To create the source, you need permission to read all event logs to make sure that the new source name is unique. Inaccessible logs: Security.

Yesterday this error came for one of the deployment on production. But the same deployment was working fine on staging environment. A message received by adapter “MSMQ” on receive location “RL_XYZ_RES_09” with URI “FORMATNAME:DIRECT=OS:.\PRIVATE$\XYZ_RES_09” is suspended. Error details: There was a failure executing the receive pipeline: “BTSHttpDecoder.DecoderPipeline, BTSHttpDecoder, Version=1.0.0.0, Culture=neutral, PublicKeyToken=5793a821957af7d1” Source: “MessageDecoderPipelineComponent_F09” Receive Port:

Read More

BizTalk Project Template is missing in Visual Studio 2012

I ran couple of times into this issue so thought of documenting it here. So first of all development of BizTalk Server 2013 application is only supported on Visual Studio 2012. In short, to get BizTalk Project Template do the following steps: Install Visual Studio 2012 Install SQLServer2012 or SQLServer2008R2 SP1 Install BizTalk Server 2013  – Select (mark

Read More

Never miss a good story!

 Subscribe to my blog to keep up with the latest news!