bts_DeQueueMessages Causing High DB CPU due to Delay Shape

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › bts_DeQueueMessages Causing High DB CPU due to Delay Shape

Viewing 1 reply thread
  • Author
    Posts
    • #18372

      Hi,Our BizTalk DB has been slowly using more and more CPU as time goes on.  One of the highest CPU items we discovered was the bts_DeQueueMessages_HOST store procedure.  When we viewed the code we noticed that it was selecting from the HOSTQ table which for some reason had over 700,000 rows.  When we investigated further we noticed that the majority of the entries had a dtValid value set to some date well into the future.   We then realized that these were probably caused by the delay shape we have in our orchestration that fires after 365 days to self terminate.  We have several long running orchestrations that legitimately can stay around for several months but after one year they are pretty much done. We really feel that these ’delayed’ messages are clogging up the HOSTQ table and cause our new transactions to suffer.So, has anyone else encountered this?  Is it not best practice to use a timer shape to do auto termination?  And most importantly, can we manually delete the rows with dtValid dates well in the future and then manually terminate orchestrations after a year; or will this mess up the BizTalk tables?Thanks

      –Andy

    • #18382

      Well, we did a bunch of testing and determined that it didn’t hurt any of the BizTalk processing in out testing environment.  We then deleted the rows from the HOSTQ table with the dtValid date greater than a couple months from now in our production environment.  The results were amazing.  Our CPU on the DB machine drop in half and our throughput rate practically doubled.  I guess the moral is if you plan on using delay shapes, don’t do any delaying out into the distance future.

       Thanks

      –Andy

Viewing 1 reply thread
  • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.