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
Hi Thiago
your queries look good and seem very interesting to have details stats on tracking and then tune it.
We have indeed lot of performance or maintenance issues reported at Microsoft because of too much tracking enabled on Production Systems.
For sure I can test them and think to either integrate them directly in next build of MsgBoxViewer (http://blogs.technet.com/jpierauc/pages/what-is-biztalk-msgboxviewer.aspx) or in an extension MBVEXT.XML file.
In the meantime I will prepare you so a MBVEXT.XML file containing your queries that you will have just to copy in MBV folder to have your queries appearing in the “Custom Queries” list when MBV is started.
MBV is indeed easely extensible via an MBVEXT.XML file containg any custom queries, it is a topic that I will detail sooon in my blog.
So thanks for sharing these interesting queries, I will send you asap the XML file so you can test your queries from MBV yourself. Then, you can share it on your blog (or mine if you prefer) as a set of additional queries for MBV.
JP