Tracking Database Service Instances Aggregation SQL Script

This script is an example to aggregate data of Service Instances from the tracking database (BizTalkDTADb):

SELECT

    dta_Svc.strServiceName + '_' + dta_SvcSta.strState AS 'Service Name'

    , COUNT(*) AS 'Number of Hits'

    , MAX(DATEDIFF(millisecond, [dta_SI].dtStartTime, [dta_SI].dtEndTime)) AS 'Maximum Execution Time'

    , AVG(CONVERT(BIGINT, DATEDIFF(millisecond, [dta_SI].dtStartTime, [dta_SI].dtEndTime))) AS 'Average Execution Time'

    , MIN(DATEDIFF(millisecond, [dta_SI].dtStartTime, [dta_SI].dtEndTime)) AS 'Minimum Execution Time'

FROM [BizTalkDTADb].[dbo].[dta_ServiceInstances] [dta_SI]

INNER JOIN

    [BizTalkDTADb].[dbo].[dta_Services] dta_Svc ON [dta_SI].uidServiceId = dta_Svc.uidServiceId

INNER JOIN

    [BizTalkMgmtDb].[dbo].[bts_assembly] bts_ass ON dta_Svc.strAssemblyName = bts_ass.nvcFullName

INNER JOIN

    [BizTalkDTADb].[dbo].[dta_ServiceState] dta_SvcSta ON [dta_SI].nServiceStateId = dta_SvcSta.nServiceStateId

WHERE

      [dta_SI].dtEndTime IS NOT NULL

GROUP BY dta_Svc.strServiceName, dta_SvcSta.strState

ORDER BY dta_Svc.strServiceName

The sample result of the script is:

In my case, this script is further enhanced by filtering to get only the last 5 minutes tracked service instances and wrapped as a stored procedure. The data from the stored procedure is used to feed the monitoring system.