Friday, December 10, 2010

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.

1 comment: