13 May BizTalk Health Statistics – Scripts
Sometimes you want to know how many messages have passed through BizTalk. Often this question pops up from a business perspective where they want to know how the use of the environment is evolving, in either positive or negative directions. It’s also useful to set out guidelines on how much data your environment can handle and to create upgrade plans when emergency levels are being reached.There are four kinds of statistics that we always try to gather. These are:
- Messages passing through per day (In & Out)
- Instances (orchestrations & pipelines) executed per day
- Usage of host instances
- Largest message sizes
The statistics are gathered by executing SQL scripts on the Tracking Database.
FROM [BizTalkDTADb].[dbo].[dta_MessageInOutEvents] WITH (NOLOCK)
WHERE [dtInsertionTimeStamp]
BETWEEN convert(datetime, ‘2014-02-10 00:00:00’, 120) AND convert(datetime, ‘2014-02-16 23:59:59’, 120)
GROUP BY datepart(dd, [dtInsertionTimeStamp]),datepart(MM, [dtInsertionTimeStamp]) ORDER BY [dateMsg]ASC, [monthMsg]
dateMsg
|
monthMsg
|
ant
|
10
|
2
|
277262
|
11
|
2
|
381427
|
12
|
2
|
427297
|
13
|
2
|
291388
|
14
|
2
|
285235
|
15
|
2
|
3290
|
16
|
2
|
672
|
Instances per day
FROM [BizTalkDTADb].[dbo].[dta_ServiceInstances] WITH (NOLOCK)
WHERE [dtInsertionTimeStamp]
BETWEEN convert(datetime ,’2014-02-10 00:00:00′, 120) AND convert(datetime, ‘2014-02-16 23:59:59’, 120)
GROUP BY datepart(dd, [dtInsertionTimeStamp]), datepart(MM, [dtInsertionTimeStamp] )ORDER BY [dateMsg] ASC, [monthMsg]
dateMsg
|
monthMsg
|
ant
|
10
|
2
|
127369
|
11
|
2
|
183848
|
12
|
2
|
201597
|
13
|
2
|
138431
|
14
|
2
|
132828
|
15
|
2
|
1536
|
16
|
2
|
314
|
Usage of host instances
FROM [BizTalkDTADb].[dbo].[dta_ServiceInstances] WITH (NOLOCK)
INNER JOIN [BizTalkDTADb].[dbo].[dta_Host] WITH (NOLOCK) ON
[dta_ServiceInstances].[nHostId]=[dta_Host].[nHostId]
WHERE [dtInsertionTimeStamp]
BETWEEN convert(datetime ,’2014-02-10 00:00:00′, 120) AND convert(datetime, ‘2014-02-10 23:59:59’, 120)
GROUP BY [dta_Host].[strHostName] ORDER BY [dta_Host].[strHostName] ASC
ant
|
strHostName
|
303
|
NWND_BAM
|
301
|
NWND_BizTalkServerAdministation
|
23217
|
NWND_BizTalkServerApplication
|
15
|
NWND_ClusteredHost_RCV
|
18
|
NWND_ClusteredHost_SND
|
48481
|
NWND_MQConnectionHost_RCV
|
18127
|
NWND_MQConnectionHost_SND
|
10836
|
NWND_SAPProcessingHost
|
164
|
NWND_SAPLendingHost
|
2653
|
NWND_SIEBELProcessingHost
|
16746
|
NWND_TECHNICOMProcessingHost
|
6508
|
NWND_WCFHost
|
Largest message(s)
FROM [BizTalkDTADb].[dbo].[dta_MessageInOutEvents] WITH (NOLOCK)
INNER JOIN [BizTalkDTADb].[dbo].[dta_SchemaName] WITH (NOLOCK) ON
[dta_MessageInOutEvents].[nSchemaId]=[dta_SchemaName].[nSchemaId]
WHERE [dtInsertionTimeStamp]
BETWEEN convert(datetime ,’2014-01-01 00:00:00′, 120) AND convert(datetime, ‘2014-01-01 23:59:59’, 120)
ORDER BY [nMessageSize] DESC
nMessageSize
|
strSchemaName
|
28272537
|
http://NWD.DataSchema.Issuer.Schemas#Issuers
|
28272537
|
|
28272537
|
|
28255369
|
|
28255369
|