I’ve written a few queries to get counts from a BizTalk tracking database – grouped by schema, schema per host, ports, and orchestration event tracking – to help me and the team advise out clients on what tracking should be turned off on their BizTalk environment. They go well together with the report you get from the MsgBoxViewer tool listing ports, orchestrations, pipelines, and what tracking is enabled on them.
Please note that the “total” columns cannot be taken literally: they only have the size of the main message as it goes through BizTalk, but doesn’t include the size of the context or anything else tracked with it, and some rows depending on where they were tracked don’t have the message size column populated. A combination of count and total should be used for any decision.
———————————————————–
USE BizTalkDTADb
GO
SET NOCOUNT ON
–Count and size per message type across all hosts
PRINT ‘Count and size per message type across all hosts’
select
sn.strSchemaName ‘Schema Name’,
sum(mioe.nMessageSize) ‘Total (Bytes)’,
(sum(mioe.nMessageSize)/1024)/1024 ‘Total (MBytes)’,
count(sn.strSchemaName) ‘Count’
from dta_MessageInOutEvents mioe WITH (READPAST)
left join dta_ServiceInstances si on mioe.uidActivityId = si.uidActivityId
left join dta_SchemaName sn on mioe.nSchemaId = sn.nSchemaId
where uidMessageInstanceId in (
select uidMessageID from tracking_Parts1
UNION
select uidMessageID from tracking_Parts2)
group by sn.strSchemaName
order by ‘Count’ desc, ‘Schema Name’ asc
–Count and size per message type per service type across all hosts
PRINT ‘Count and size per message type per service type across all hosts’
select
sn.strSchemaName ‘Schema Name’,
sc.nvcName ‘Type’,
sum(mioe.nMessageSize) ‘Total (Bytes)’,
(sum(mioe.nMessageSize)/1024)/1024 ‘Total (MBytes)’,
count(sn.strSchemaName) ‘Count’
from dta_MessageInOutEvents mioe WITH (READPAST)
left join dta_ServiceInstances si on mioe.uidActivityId = si.uidActivityId
left join dta_SchemaName sn on mioe.nSchemaId = sn.nSchemaId
left join BizTalkMsgBoxDb.dbo.ServiceClasses sc on si.uidServiceClassId = sc.uidServiceClassID
where uidMessageInstanceId in (
select uidMessageID from tracking_Parts1
UNION
select uidMessageID from tracking_Parts2)
group by sn.strSchemaName, sc.nvcName
order by ‘Count’ desc
–Count and size per message type per host
PRINT ‘Count and size per message type per host’
select
h.strHostName ‘Host’,
sn.strSchemaName ‘Schema Name’,
sum(mioe.nMessageSize) ‘Total (Bytes)’,
(sum(mioe.nMessageSize)/1024)/1024 ‘Total (MBytes)’,
count(sn.strSchemaName) ‘Count’
from dta_MessageInOutEvents mioe WITH (READPAST)
left join dta_ServiceInstances si on mioe.uidActivityId = si.uidActivityId
left join dta_Host h on si.nHostId = h.nHostId
left join dta_SchemaName sn on mioe.nSchemaId = sn.nSchemaId
where uidMessageInstanceId in (
select uidMessageID from tracking_Parts1
UNION
select uidMessageID from tracking_Parts2)
group by sn.strSchemaName, h.strHostName
order by ‘Host’, ‘Count’ desc, ‘Schema Name’ asc
–Count and size per port
PRINT ‘Count and size per port’
select
pn.strPortName ‘Port’,
sc.nvcName ‘Type’,
sum(mioe.nMessageSize) ‘Total (Bytes)’,
(sum(mioe.nMessageSize)/1024)/1024 ‘Total (MBytes)’,
count(pn.strPortName) ‘Count’
from dta_MessageInOutEvents mioe WITH (READPAST)
left join dta_ServiceInstances si on mioe.uidActivityId = si.uidActivityId
left join dta_PortName pn on mioe.nPortId = pn.nPortId
left join BizTalkMsgBoxDb.dbo.ServiceClasses sc on si.uidServiceClassId = sc.uidServiceClassID
where uidMessageInstanceId in (
select uidMessageID from tracking_Parts1
UNION
select uidMessageID from tracking_Parts2)
group by strPortName, nvcName
order by ‘Count’ desc
–Orchestration debug trace count (the ‘Track Event’ options on the orchestration tracking)
select
o.nvcFullName ‘Orchestration’,
count(o.nvcFullName) ‘Count’
from dta_DebugTrace dt with (READPAST)
join dta_ServiceInstances si on dt.uidServiceInstanceID = si.uidServiceInstanceId
left join BizTalkMgmtDb.dbo.bts_Orchestration o on si.uidServiceId = o.uidGUID
group by o.nvcFullName
order by ‘Count’ desc
———————————————————–
Enjoy!
– Thiago Almeida
RSS
