BizTalk Artifact Duration Aggregations

I’m working on another BizTalk performance gig and created a few custom HAT queries for measuring BizTalk artifact durations. I like to call them BizTalk Artifact Duration Aggregations or BADAggs for short – pun intended. ;-)

To aggregate artifact durations for the last x amount of time, you can modify the time range by modifying the dateadd parameters on line 2 of the following query. In this case, this query aggregates the durations of all of the artifacts that executed within the last 30 minutes.

declare @Timestamp as datetime
set @Timestamp = dateadd(minute, -30, GETUTCDATE())
SELECT
                [Service/Name],
                AVG([ServiceInstance/Duration]) as AverageDuration
FROM dbo.dtav_ServiceFacts sf WITH (READPAST)
WHERE [ServiceInstance/StartTime] > @Timestamp
GROUP BY [Service/Name]
ORDER BY AverageDuration desc

This next query allows you to choose a begin and end time range to aggregate the durations of the artifacts.

declare @BeginTime as datetime
declare @EndTime as datetime
set @BeginTime = CAST('2008-05-04 00:00:00.000'as datetime)
set @EndTime = CAST('2008-05-06 00:00:00.000'as datetime)
SELECT
                [Service/Name],
                AVG([ServiceInstance/Duration]) as AverageDuration
FROM dbo.dtav_ServiceFacts sf WITH (READPAST)
WHERE [ServiceInstance/StartTime] > @BeginTime
AND       [ServiceInstance/StartTime] < @EndTime
GROUP BY [Service/Name]
ORDER BY AverageDuration desc

Both of these return results similar to this:

[Service/Name], AverageDuration
Microsoft.BizTalk.DefaultPipelines.XMLReceive,1057
Microsoft.Samples.BizTalk.ConsumeWebService.ReceivePOandSubmitToWS,7375
Microsoft.BizTalk.DefaultPipelines.PassThruTransmit,1115

Enjoy!

Comments

• Anonymous
  January 01, 2003
  The times returned are in seconds, right?