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>