Update: The tracking query as a TRQ file is available here, per a comment on this post.

I just got back from a week at the "Microsoft SOA & Business Process" conference in Redmond.  Lots of discussion on BizTalk 2006 R2, the new .NET Adapter Framework, Windows Communication Foundation (WCF), Windows Workflow (WF), Microsoft Office Sharepoint Server 2007 (MOSS).  Great stuff.

I did a session at the conference on Friday titled "Applying Maximum Sustainable Throughput to a Management/Operations Strategy".  (OK, the title may have scared a few people off...) The content was an extension of what I presented to the Twin Cities BizTalk User Group in September.  The overall theme was relating what you learn during your performance testing phase to your operations strategy, and ensuring that you have application-level metrics that represent operational boundaries you would like to "live within".  In particular, I discussed how to formulate your performance goals, how to measure against them as operational parameters, and how to proactively monitor with a tool such as Microsoft Operations Manager (MOM).  We went on to discuss how to choose the workloads (e.g. send, receive, or orchestration work) for new servers that you place in your BizTalk group, depending on what you see within various BizTalk work queues and throttling states.  I've put the slides for the session here for your perusal. 

One of the demos showed a HAT query I've worked up which will report on how many times each orchestration has run within a given time window, along with the percent of total transaction volume it represents, time duration, and activity rates (per second, per minute, and per hour). You can put the following query in a .TRQ file (like OrchestrationDurationsAndRates.trq) and the put it within C:\Program Files\Microsoft BizTalk Server 2006\Tracking\Queries (or the equivalent on your server.) It will then appear within the "Queries" menu in HAT. This can be a genuinely useful query to have at your disposal, both during performance testing and in production.  You can run this in Query Analyzer if you prefer - just uncomment the lines that define UtcOffsetMin.  Note that rate calculations are dividing across the entire time window, which assumes a fairly steady traffic pattern.

If you're trying to determine compliance within an SLA such as "95% of all requests must be satisfied within 15 seconds or less", you might add an item to the select clause below along the lines of "average(service duration) + (2*stddev(service duration))", if the distribution of your transaction durations tends to be normal (per your high school stats class.)

declare @beginTime as datetime
declare @endTime as datetime

declare @TotalCountInWindow as int

--
--  SET THE TIME WINDOW FOR YOUR QUERY HERE
--
select @beginTime =DateAdd(hour, -2, GetDate())
select @endTime = GetDate()

-- Only need to uncomment this select when inside of query analyzer,
-- where UtcOffsetMin won't be defined.
-- declare @UtcOffsetMin as int
-- select @UtcOffsetMin = 
-- -1*DateDiff("mi",CONVERT(varchar,GetDate(),0),CONVERT(varchar,GetUTCDate(),0))

select @beginTime = dateadd(minute,-1*@UtcOffsetMin,@beginTime)
select @endTime = dateadd(minute,-1*@UtcOffsetMin,@endTime)

SELECT @TotalCountInWindow = count(*)
FROM dbo.dtav_ServiceFacts sf WITH (READPAST)
where [ServiceInstance/StartTime] between
@beginTime and @endTime
and [ServiceInstance/State] = 'Completed'
and [Service/Type] = 'Orchestration'


SELECT 
[Service/Name],
count(*) as TotalCount,
(cast(count(*) as float)/cast(@TotalCountInWindow as float))*100 as PercentOfTotal,
avg([ServiceInstance/Duration]/1000.0) as AverageDuration,
min([ServiceInstance/Duration]/1000.0) as MinDuration,
max([ServiceInstance/Duration]/1000.0) as MaxDuration,
stdev([ServiceInstance/Duration]/1000.0) as StdDevDuration,
count(*)/DateDiff("hh",@beginTime,@endTime) as perHour,
count(*)/DateDiff("mi",@beginTime,@endTime) as perMinute,
cast(count(*) as float)/DateDiff("ss",@beginTime,@endTime) as perSecond

FROM dbo.dtav_ServiceFacts sf WITH (READPAST)
where [ServiceInstance/StartTime] between
@beginTime and @endTime
--and  [ServiceInstance/Duration] > 0
and [ServiceInstance/State] = 'Completed'
and [Service/Type] = 'Orchestration'
group by [Service/Name]

Got your own favorite HAT query? Post it in the comments - maybe we'll start a new section for them. Some shops deploy their custom HAT queries directly with their BizTalk applications...For Deployment Framework folks, that looks as follows (assuming a project subdirectory called DeployedHATQueries):

	
<target name="deployHatQueries">
  <copy todir="${btsDir}\Tracking\Queries" overwrite="true">
      <fileset basedir="DeployedHATQueries">
         <include name="**\*.trq" />
      </fileset>
  </copy>
</target>