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.
Messages per day
SELECT datepart(dd, [dtInsertionTimeStamp]) as dateMsg, datepart(MM, [dtInsertionTimeStamp]) as monthMsg, count(Convert(char(10), [dtInsertionTimeStamp], 108)) as ant
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]
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]
The “BETWEEN” part can be filled in with a timeframe of several days (in case your tracking database keeps all data on these days. A result as the following will be received:
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
SELECT datepart(dd, [dtInsertionTimeStamp]) as dateMsg, datepart(MM, [dtInsertionTimeStamp]) as monthMsg, count(Convert(char(10), [dtInsertionTimeStamp], 108)) as ant
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]
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]
Also in this script, the “BETWEEN” part can be filled in with a timeframe. Following result is received:
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
SELECT count(Convert(char(10), [dta_ServiceInstances].[dtInsertionTimeStamp], 108)) as ant, [dta_Host].[strHostName]
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
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
In this script, a choice can be made in terms of grouping. When you want to have just the numbers of a timeframe (for example: between feb 01 and feb 15, there were 23,765 calls of MQHostReceive), just add the timeframe in the “BETWEEN” field. If you want the data grouped by day, the script needs to be executed once for every day you want the statistics. Set the “BETWEEN” to 2014-02-12 00:00:00 and 2014-02-12 23:59:59 for example, and you will get the following results:
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)
SELECT TOP 5 [dta_MessageInOutEvents].[nMessageSize], [dta_SchemaName].[strSchemaName]
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
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
This script will get a top 5 of all the largest messages that passed through BizTalk in the given timeframe. A result like following will pop up:
nMessageSize
|
strSchemaName
|
28272537
|
http://NWD.DataSchema.Issuer.Schemas#Issuers
|
28272537
| |
28272537
| |
28255369
| |
28255369
|
If you got any remarks, please leave them in the comment section!
Andrew De Bruyne (@draitnn)
